public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bhupendra Babu <[email protected]>
To: Kenny Bachman <[email protected]>
Cc: [email protected]
Subject: Re: postgresql-14 slow query
Date: Fri, 15 Apr 2022 21:14:25 -0700
Message-ID: <CAOEE2FfCw8=mvUjpJO56AezzRdA1LcjeAHyYKsfviRNXTrK-bA@mail.gmail.com> (raw)
In-Reply-To: <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
References: <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
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 <[email protected]>
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
>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: postgresql-14 slow query
In-Reply-To: <CAOEE2FfCw8=mvUjpJO56AezzRdA1LcjeAHyYKsfviRNXTrK-bA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox