Postgres 13 Sort Performance

Question

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

Can you post the EXPLAIN (ANALYZE,BUFFERS) from the query without the ORDER BY? — dwhitemv, Oct 27 '21 at 05:08
Thanks for the response. Updated the question with EXPLAIN (ANALYZE,BUFFERS) without order by — Abul Hassan, Oct 27 '21 at 05:14
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 '21 at 05:30
@AlexYu your solution has partially worked. The execution is down to 9 seconds. It still does not solve the problem completely though — Abul Hassan, Oct 27 '21 at 06:41
@GerardH.Pille Can you please explain how? And what could be improved? — Abul Hassan, Oct 27 '21 at 07:19
@AbulHassan A. Experiment with moving fields from INCLUDE to list of index fields. B. CLUSTER stock_quant USING ui_stock_quant and then ANALYZE stock_quant; - although that starts feeling like cheating. — Alex Yu, Oct 27 '21 at 07:26
@AbulHassan, without the table ddl, I can only guess. 11 of your 12 indexes are single column. Most of those won't be very selective, ie. useless. They will slow down IUD statements. I never saw an index on quantity before. Do you often look for products of which you have 35.5 in stock? You should base your indexes on which ones needed for referential integrity (index on parent column of child table) and those needed for the queries that need an immediate answer. Those are based on the where clause of your queries, and combined! (continued) — Gerard H. Pille, Oct 27 '21 at 11:36
@AbulHassan Taking your query as an example, I'd have one index on company_id, product_id and location_id. Perhaps adding reservation_id, if the previous three columns are not selective enough (if most have no reservation_id, then leave it out). Drop the other indexes, certainly those which slow you down, as in your problem, and then those that aren't used (https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW) You can improve performance a lot, without touching the queries. — Gerard H. Pille, Oct 27 '21 at 11:40
Why is the estimate so wrong? 4074 estimated versus 0 found. Start breaking it down to individual criterions, or pairs of criteria, and see where the estimate falls apart. — jjanes, Oct 27 '21 at 16:15

dwhitemv · Answer 1 · 2021-10-27T16:19:55.767

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.

edited Oct 27 '21 at 16:19

answered Oct 27 '21 at 05:30

dwhitemv

1,567
4
7

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 '21 at 05: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. – Abul Hassan Oct 27 '21 at 07:21
That is not an excuse. If your ORM cannot do it by itself, you have to write SQL. – Laurenz Albe Oct 27 '21 at 07: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 '21 at 16:13

Postgres 13 Sort Performance

1 Answers1