Feature #1592

Add Automatic Creation of Database Indexes

Added by Pavan Rikhi 12 months ago. Updated 11 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:Pavan Rikhi% Done:

0%

Category:PerformanceSpent time:-
Target version:v2.0.0 - Post-Migration Issues
Easy Pickings:

Description

This is out of scope for the Persistent/Esqueleto libraries.

Probably want one on each ForeignKey & PrimaryKey. Definitely need the order_id field, & id fields for the Customer & Address table to speed up the Admin Order List page.

https://use-the-index-luke.com/

https://hackage.haskell.org/package/dbmigrations-2.0.0

https://github.com/ankane/pghero

https://github.com/ankane/dexter/


I added these indexes manually:


CREATE INDEX CONCURRENTLY ON address (address_one);
CREATE INDEX CONCURRENTLY ON address (customer_id);
CREATE INDEX CONCURRENTLY ON cart (customer_id);
CREATE INDEX CONCURRENTLY ON cart_item (cart_id, product_variant_id);
CREATE INDEX CONCURRENTLY ON category_sale (category_ids);
CREATE INDEX CONCURRENTLY ON "order" (customer_id);
CREATE INDEX CONCURRENTLY ON "order" (billing_address_id);
CREATE INDEX CONCURRENTLY ON "order" (shipping_address_id);
CREATE INDEX CONCURRENTLY ON "order" (coupon_id);
CREATE INDEX CONCURRENTLY ON order_line_item (order_id);
CREATE INDEX CONCURRENTLY ON order_product (order_id, product_variant_id);
CREATE INDEX CONCURRENTLY ON product (main_category);
CREATE INDEX CONCURRENTLY ON product_sale (product_variant_id);
CREATE INDEX CONCURRENTLY ON review (customer_id, variant_id);

History

#1 Updated by Pavan Rikhi 12 months ago

  • Description updated (diff)

#2 Updated by Pavan Rikhi 11 months ago

during the latest data migration, pghero suggested creatingthe following:

CREATE INDEX CONCURRENTLY ON address (address_one);

CREATE INDEX CONCURRENTLY ON address (customer_id);

#3 Updated by Pavan Rikhi 11 months ago

  • Target version changed from v1.00.00 - Deployment to v2.0.0 - Post-Migration Issues

#4 Updated by Pavan Rikhi 11 months ago

  • Description updated (diff)

Also available in: Atom PDF