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