Re: Query performance issue

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 <mlewis(at)entrata(dot)com> wrote:

On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> 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.