Add is_false as 2nd column in index.
So your index will be state_id,is_false ,ordrr_id with or without desc.
Since your plan shows lot of data to filter. New index wi help filter less rows. Of course you need to check and analyze the volum of how your data is organized.
On Fri, Apr 15, 2022, 1:59 PM Kenny Bachman <kenny.bachman17@xxxxxxxxx> wrote:
Hello Team,How can I tune this query? It got even slower when I created the index for (state_id, order_id desc). The following explain analyze output is without an index. It takes 13 seconds if I create that index. Could you help me?Thank you so much for your help.SELECT DISTINCT ON (order_history.order_id) order_id, order_history.creation_date AS c_date
FROM work.order_history WHERE (order_history.state_id = ANY (ARRAY[30, 51, 63, 136, 195, 233, 348])) AND order_history.is_false = 0
ORDER BY order_history.order_id DESC;EXPLAIN ANALYZE output:Unique (cost=672007.46..1519683.55 rows=206423 width=12) (actual time=1701.420..3439.095 rows=2049357 loops=1)
-> Gather Merge (cost=672007.46..1502346.48 rows=6934827 width=12) (actual time=1701.419..2989.243 rows=6891551 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Sort (cost=671007.40..675341.67 rows=1733707 width=12) (actual time=1657.609..1799.723 rows=1378310 loops=5)
Sort Key: order_id DESC
Sort Method: external merge Disk: 38960kB
Worker 0: Sort Method: external merge Disk: 31488kB
Worker 1: Sort Method: external merge Disk: 36120kB
Worker 2: Sort Method: external merge Disk: 31368kB
Worker 3: Sort Method: external merge Disk: 36152kB
-> Parallel Seq Scan on order_history (cost=0.00..473993.00 rows=1733707 width=12) (actual time=0.041..1211.485 rows=1378310 loops=5)
Filter: ((is_false = 0) AND (state_id = ANY ('{30,51,63,136,195,233,348}'::integer[])))
Rows Removed by Filter: 3268432
Planning Time: 0.405 ms
Execution Time: 3510.433 ms
