"for fun" try doing
Set enable_seqscan=off
And try the queries again. This will discourage it from doing sequential scans and use indexes if possible. If it still does a sequential scan, then there's some reason it can't use the indexes. If it uses indexes now, then the planner must think that the sequential scan would be quicker
On 15 April 2022 22:00:07 Kenny Bachman <kenny.bachman17@gmail.com> 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
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
