Re: [PERFORM] Slow query: bitmap scan troubles

On 7 January 2013 17:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> That gives a formula of
>
> cpu_operator_cost * log2(N) + cpu_operator_cost * 50 * (H+2)
>
> This would lead to the behavior depicted in the attached plot, wherein
> I've modified the comparison lines (historical, 9.2, and HEAD behaviors)
> to include the existing 100 * cpu_operator_cost startup cost charge in
> addition to the fudge factor we've been discussing so far. The new
> proposed curve is a bit above the historical curve for indexes with
> 250-5000 tuples, but the value is still quite small there, so I'm not
> too worried about that. The people who've been complaining about 9.2's
> behavior have indexes much larger than that.
>
> Thoughts?

Again, this depends on N and H, so thats good.

I think my retinas detached while reading your explanation, but I'm a
long way from coming up with a better or more principled one.

If we can describe this as a heuristic that appears to fit the
observed costs, we may keep the door open for something better a
little later.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services