On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu <yavuzselim.sertoglu@xxxxxxxxxxxxxxxx> wrote:
Hi all,
I have a problem with my query. Query always using parallel bitmap heap scan. I've created an index with all where conditions and id but query does not this index and continue to use bitmapscan. So I decided disable bitmap scan for testing. And after that, things became strange. Cost is higher, execution time is lower.
A 20% difference in speed is unlikely to make or break you. Is it even worth worrying about?
But I want to use index_only_scan because index have all column that query need. No need to access table.
Your table is not very well vacuumed, so there is need to access it (9010 times to get 6115 rows, which seems like quite an anti-feat; but I don't know which of those numbers are averaged over loops/parallel workers, versus summed over them). Vacuuming your table will not only make the index-only scan look faster to the planner, but also actually be faster.
The difference in timing could easily be down to one query warming the cache for the other. Are these timings fully reproducible altering execution orders back and forth? And they have different degrees of parallelism, what happens if you disable parallelism to simplify the analysis?
It is doing index_only_scan when disabling bitmap scan but I cannot disable bitmap scan for cluster wide. There are other queries...
Can you help me to solve the issue?
Cranking up effective_cache_size can make index scans look better in comparison to bitmap scans, without changing a lot of other stuff. This still holds even for index-only-scan, in cases where the planner knows the table to be poorly vacuumed.
But moving the column tested for inequality to the end of the index would be probably make much more of a difference, regardless of which plan it chooses.
Cheers,
Jeff
