From: Thomas Kellerer <shammat@gmx.net>
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Query performance issue
Date: Fri, 4 Sep 2020 23:23:38 +0200
Message-ID: <b78f661a-77a8-c620-2f65-94282fd68f52@gmx.net> (raw)
In-Reply-To: <975305787.3395625.1599254321500@mail.yahoo.com>
References: <975305787.3395625.1599254321500.ref@mail.yahoo.com>
<975305787.3395625.1599254321500@mail.yahoo.com>
Nagaraj Raj schrieb am 04.09.2020 um 23:18:
> I have a query which will more often run on DB and very slow and it
> is doing 'seqscan'. I was trying to optimize it by adding indexes in
> different ways but nothing helps.
>
> EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime
> from (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number() over (partition by serial_no order by eventtime desc) as mpos
> from receiving_item_delivered_received
> where eventtype='LineItemdetailsReceived'
> and replenishmenttype = 'DC2SWARRANTY'
> and coalesce(serial_no,'') <> ''
> ) Rec where mpos = 1;
>
>
> Query Planner:
>
> "Subquery Scan on rec (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451 loops=1)"
> " Filter: (rec.mpos = 1)"
> " Rows Removed by Filter: 19900"
> " -> WindowAgg (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351 loops=1)"
> " -> Sort (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351 loops=1)"
> " Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC"
> " Sort Method: external merge Disk: 17424kB"
> " -> Seq Scan on receiving_item_delivered_received (cost=0.00..28777.82 rows=352006 width=39) (actual time=0.011..184.677 rows=353351 loops=1)"
> " Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))"
> " Rows Removed by Filter: 55953"
> "Planning Time: 0.197 ms"
> "Execution Time: 3466.985 ms"
The query retrieves nearly all rows from the table 353351 of 409304 and the Seq Scan takes less than 200ms, so that's not your bottleneck.
Adding indexes won't change that.
The majority of the time is spent in the sort step which is done on disk.
Try to increase work_mem until the "external merge" disappears and is done in memory.
Thomas
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: pgsql-performance@postgresql.org
Cc: shammat@gmx.net, pgsql-performance@lists.postgresql.org
Subject: Re: Query performance issue
In-Reply-To: <b78f661a-77a8-c620-2f65-94282fd68f52@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox