I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column.
This is for a dynamic pagination page where the query is constructed on demand, and the application caches the primary key in the query's specified order.
For this query
explain analyze SELECT supplier_management.buyer_purchase_order_id FROM supplier_management ORDER BY item_description DESC, item_number DESC, order_type ASC, possession_date DESC, shipment_type DESC, store_type DESC
I get these results:
Sort (cost=51026.98..51750.35 rows=289348 width=72) (actual time=8229.280..12349.596 rows=289348 loops=1)
Sort Key: item_description, item_number, order_type, possession_date, shipment_type, store_type
Sort Method: external merge Disk: 24744kB
-> Seq Scan on supplier_management (cost=0.00..10876.48 rows=289348 width=72) (actual time=0.015..187.426 rows=289348 loops=1)
Total runtime: 12407.064 ms
How can the performance of a multi-column sort be improved? Or should I just do it in C++?
buyer_purchase_order_id bigint supplier_number bigint supplier_name character varying purchase_order_number bigint store_number integer item_number bigint item_description character varying project_type character varying order_date integer requested_arrival_date integer department character varying store_type character varying shipment_type character varying order_type character varying quantity_ordered integer quantity_allocation integer quantity_staged integer quantity_shipped integer quantity_received integer in_stock_date integer in_stock_date_visible_on integer show_red boolean requested_arrival_date_plus_four_business_days integer supplier_status character varying notes_comments character varying requested_arrival_date_color character varying grand_opening_date integer possession_date integer consolidator_name character varying real_requested_arrival_date_plus_four_business_days integer
character varying exceeds the length limit for indexing; however, there is some duplication. Would it be more efficient to put the actual values in another table, normalize, and join on the related integer columns?