Re: postgresql-14 slow query

"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(dot)bachman17(at)gmail(dot)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

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe