Hi Michael,
I created an index as suggested, it improved. I was tried with partial index
but the planner not using it.
also, there is no difference even with timing OFF. ktbv : Optimization for:
plan #HaOx | explain.depesz.com
|
|
| |
ktbv : Optimization for: plan #HaOx | explain.depesz.com
|
|
|
Thanks,Rj
On Saturday, September 5, 2020, 06:42:31 AM PDT, Michael Lewis
<[email protected]> wrote:
On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj <[email protected]> wrote:
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,
64-bit
vCPU = 64RAM = 512show shared_buffers = 355 GBshow work_mem = 214 MB
show maintenance_work_mem = 8363MBshow effective_cache_size = 355 GB
I'm not very familiar with Aurora, but I would certainly try the explain
analyze with timing OFF and verify that the total time is similar. If the
system clock is slow to read, execution plans can be significantly slower just
because of the cost to measure each step.
That sort being so slow is perplexing. Did you do the two column or four column
index I suggested?
Obviously it depends on your use case and how much you want to tune this
specific query, but you could always try a partial index matching the where
condition and just index the other two columns to avoid the sort.
