1

Sort performance on one of our query is very bad that it takes up to 14 seconds to run the query.

Here is the Query:

SELECT "stock_quant".id 
FROM "stock_quant" 
WHERE ((((("stock_quant"."qty" > 0.0)  
      AND "stock_quant"."reservation_id" IS NULL )  
      AND ("stock_quant"."location_id" in (34)))
      AND ("stock_quant"."product_id" = 203330))  
      AND ("stock_quant"."company_id" = 5)) 
ORDER BY "stock_quant"."in_date" ,"stock_quant"."id"   
limit 10;

When used Explain, this is what postgres says

explain (analyze,buffers) SELECT "stock_quant".id FROM "stock_quant" WHERE ((((("stock_quant"."qty" > 0.0)  AND  "stock_quant"."reservation_id" IS NULL )  AND  ("stock_quant"."location_id" in (34)))  AND  ("stock_quant"."product_id" = 203330))  AND  ("stock_quant"."company_id" = 5)) ORDER BY "stock_quant"."in_date" ,"stock_quant"."id"   limit 10;
Limit  (cost=0.56..4723.78 rows=10 width=12) (actual time=15754.259..15754.260 rows=0 loops=1)
    Buffers: shared hit=9988201 read=94226
    ->  Index Scan using stock_quant_multisort_idx on stock_quant  (cost=0.56..1923768.25 rows=4073 width=12) (actual time=15754.257..15754.257 rows=0 loops=1)
                 Filter: ((reservation_id IS NULL) AND (qty > '0'::double precision) AND (location_id = 34) AND (product_id = 203330) AND (company_id = 5))
                 Rows Removed by Filter: 24052667
                 Buffers: shared hit=9988201 read=94226  
Planning Time: 0.291 ms  
Execution Time: 15754.288 ms 
(8 rows)
explain SELECT "stock_quant".id FROM "stock_quant" WHERE ((((("stock_quant"."qty" > 0.0)  AND  "stock_quant"."reservation_id" IS NULL )  AND  ("stock_quant"."location_id" in (34)))  AND  ("stock_quant"."product_id" = 203330))  AND  ("stock_quant"."company_id" = 5)) ORDER BY "stock_quant"."in_date" ,"stock_quant"."id"   limit 10;
Limit  (cost=0.56..4723.82 rows=10 width=12)
    ->  Index Scan using stock_quant_multisort_idx on stock_quant  (cost=0.56..1923781.40 rows=4073 width=12)
                 Filter: ((reservation_id IS NULL) AND (qty > '0'::double precision) AND (location_id = 34) AND (product_id = 203330) AND (company_id = 5)) (3 rows)

And here are the indexes in the table:

"stock_quant_pkey" PRIMARY KEY, btree (id)
    "stock_quant_company_id_index" btree (company_id)
    "stock_quant_location_id_index" btree (location_id)
    "stock_quant_lot_id_index" btree (lot_id)
    "stock_quant_multisort_idx" btree (in_date, id)
    "stock_quant_owner_id_index" btree (owner_id)
    "stock_quant_package_id_index" btree (package_id)
    "stock_quant_product_id_index" btree (product_id)
    "stock_quant_product_location_index" btree (product_id, location_id, company_id, qty, in_date, reservation_id)
    "stock_quant_propagated_from_id_index" btree (propagated_from_id)
    "stock_quant_qty_index" btree (qty)
    "stock_quant_reservation_id_index" btree (reservation_id)

Work Mem is set at 512MB

Any idea what needs to be changed? Without sort, the same query executes in less than 200ms.

Update: Explain Analyze without Order by

explain (analyze,buffers) SELECT "stock_quant".id FROM "stock_quant" WHERE ((((("stock_quant"."qty" > 0.0)  AND  "stock_quant"."reservation_id" IS NULL )  AND  ("stock_quant"."location_id" in (34)))  AND  ("stock_quant"."product_id" = 203330))  AND  ("stock_quant"."company_id" = 5))  limit 10;
                                                                     QUERY PLAN

Limit  (cost=0.56..33.76 rows=10 width=4) (actual time=0.661..0.662 rows=0 loops=1)
   Buffers: shared hit=2 read=2
   ->  Index Scan using stock_quant_product_location_index on stock_quant  (cost=0.56..13524.04 rows=4074 width=4) (actual time=0.660..0.660 rows=0 loops=1)
         Index Cond: ((product_id = 203330) AND (location_id = 34) AND (company_id = 5) AND (qty > '0'::double precision) AND (reservation_id IS NULL))
         Buffers: shared hit=2 read=2
 Planning:
   Buffers: shared hit=248 read=16
 Planning Time: 7.005 ms
 Execution Time: 0.691 ms
10
  • 2
    Can you post the EXPLAIN (ANALYZE,BUFFERS) from the query without the ORDER BY?
    – dwhitemv
    Oct 27 at 5:08
  • 1
    Thanks for the response. Updated the question with EXPLAIN (ANALYZE,BUFFERS) without order by Oct 27 at 5:14
  • 1
    Question: Do you have possibility to define or modify existing indexes? If you're able to do that - then solution must be trivial: CREATE UNIQUE INDEX ui_stock_quant ON stock_quant (in_date, id) INCLUDE (qty, reservation_id, location_id, product_id, company_id) - maybe some fields move from INCLUDE-fields to main fields. This way you will get INDEX ONLY scan with ORDER BY optimized by index.
    – Alex Yu
    Oct 27 at 5:30
  • @AlexYu your solution has partially worked. The execution is down to 9 seconds. It still does not solve the problem completely though Oct 27 at 6:41
  • Your indexes do more harm than good. Oct 27 at 6:50
0

PostgreSQL thinks that scanning the index in sorted order is faster than retrieving and sorting the resultant columns, which is clearly not true in this case. I would suggest running ANALYZE stock_quant first and see if that helps, and if not, modify your query to disqualify using an index to avoid a Sort step:

SELECT "stock_quant".id 
FROM "stock_quant" 
WHERE ((((("stock_quant"."qty" > 0.0)  
      AND "stock_quant"."reservation_id" IS NULL )  
      AND ("stock_quant"."location_id" in (34)))
      AND ("stock_quant"."product_id" = 203330))  
      AND ("stock_quant"."company_id" = 5)) 
ORDER BY "stock_quant"."in_date"+0 ,"stock_quant"."id"+0   
limit 10;

You might have to take the +0 off the in_date term depending on its type. Reference this answer for how this trick works.

4
  • Hm. Than query will go with Index Scan or with Bitmap Scan by other indexes. And id will become unsupported by index. Maybe it will work
    – Alex Yu
    Oct 27 at 5:35
  • Unfortunately, I cannot edit the query because its generated by Odoo (ERP Framework) ORM. And this is just once instance, there are other tables with similar issues. Oct 27 at 7:21
  • That is not an excuse. If your ORM cannot do it by itself, you have to write SQL. Oct 27 at 7:49
  • @AlexYu The concept is to get PostgreSQL to use stock_quant_product_location_index to find the rows and a regular Sort afterward. The actual query is a bit of a pathological one as it returns 0 rows but it shouldn’t need a full table or index scan to determine that.
    – dwhitemv
    Oct 27 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.