On 14 Červenec 2014, 16:00, Magers, James wrote: > Thomas, > > I would have to agree that the current results do indicate that. However, > I have run this explain analyze multiple times and the timing varies from > about 4ms to 35ms using the Bitmap Heap Scan. Here is an explain plan > from Thursday of last week that shows about 21ms. Part of the issue in > trying to isolate if the query can be faster is that once the data is > cached any way that the query is executed appears to be quicker. > > http://explain.depesz.com/s/SIX1 I think that judging the performance based on this limited number of samples is futile, especially when the plans are this fast. The measurements are easy to influence by other tasks running on the system, OS process scheduling etc. Or it might be because of memory pressure on the system, causing the important data from page cache (and thus I/O for queries accessing them). This might be the reason why you saw higher timings, and it's impossible to say based solely on explain plan from a single execution. To get meaningful numbers it's necessary to execute the query repeatedly, to eliminate caching effects. But the question is whether these caching effects will happen on production or not. (Because what if you tweak the configuration to get the best plan based on assumption that everything is cached, when it won't be in practice?) That being said, the only plan that's actually faster than the bitmap index scan (which you believe is inefficient) is this one http://explain.depesz.com/s/3wna The reason why it's not selected by the optimizer is that the cost is estimated to be 20.60, while the bitmap index scan cost is estimated as 20.38. So the optimizer decides that 20.38 is lower than 20.60, and thus chooses the bitmap index scan. What you may do is tweak cost constants, described here www.postgresql.org/docs/9.4/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS You need to increase the bitmap idex scan cost estimate, so that it's more expensive than the index scan. I'd guess that increasing the cpu_tuple_cost and/or cpu_index_tuple_cost a bit should do the trick. regards Tomas
