Re: Query performance issue

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 <https://explain.depesz.com/s/HaOx>
>
> HaOx | explain.depesz.com
>
> <https://explain.depesz.com/s/HaOx>
>
> Mem config:
>
> Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> 4.9.3, 64-bit
> vCPU = 64
> RAM = 512
> show shared_buffers = 355 GB
> show work_mem = 214 MB
> show maintenance_work_mem = 8363MB
> show 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.