Re: Query performance issue

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