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 |
|
|
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.