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.
Any suggestions?
Query:
EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select
serial_no,receivingpl
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 ser
query planner:SPJe | explain.depesz.com
|
|
| |
SPJe | explain.depesz.com
|
|
|
On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj
wrote:
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 add
On Fri, Sep 04, 2020 at 09:18:41PM +, Nagaraj Raj wrote:
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.
Any suggestions?
1) It's rather difficult to read the query
CREATE INDEX receiving_item_delivered_received
ON receiving_item_delivered_received USING btree ( eventtype,
replenishmenttype, serial_no, eventtime DESC );
>
More work_mem as Tomas suggests, but also, the above index should find the
candidate rows by the first two keys, and then be able to skip t
Note- you may need to vacuum* the table to get full benefit of index only
scan by updating the visibility map. I think index only scan is skipped in
favor of just checking visibility when the visibility map is stale.
*NOT full
Sorry, I have attached the wrong query planner, which executed in lower
environment which has fewer resources:
Updated one,eVFiF | explain.depesz.com
|
|
| |
eVFiF | explain.depesz.com
|
|
|
Thanks,RjOn Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis
wrote:
CR
"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41)
(actual time=22171.986..23549.079 rows=1236042 loops=1)" " Filter:
(rec.mpos = 1)" " Rows Removed by Filter: 228737" " Buffers: shared hit=45
read=1166951" " I/O Timings: read=29.530" " -> WindowAgg
(cost=1628601.89..1658127.45
Hi Mechel,
I added the index as you suggested and the planner going through the bitmap
index scan,heap and the new planner is,HaOx | explain.depesz.com
|
|
| |
HaOx | explain.depesz.com
|
|
|
Mem config:
Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,
