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,
"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
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
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
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
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
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
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
