Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> 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)

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

I'm fairly happy with the general shape of this formula: it has a
principled explanation and the resulting numbers appear to be sane.
The specific cost multipliers obviously are open to improvement based
on future evidence.  (In particular, I intend to code it in a way that
doesn't tie the "startup overhead" and "cost per page" numbers to be
equal, even though I'm setting them equal for the moment for lack of a
better idea.)

One issue that needs some thought is that the argument for this formula
is based entirely on thinking about b-trees.  I think it's probably
reasonable to apply it to gist, gin, and sp-gist as well, assuming we
can get some estimate of tree height for those, but it's obviously
hogwash for hash indexes.  We could possibly just take H=0 for hash,
and still apply the log2(N) part ... not so much because that is right
as because it's likely too small to matter.

			regards, tom lane

In response to

• Re: [PERFORM] Slow query: bitmap scan troubles at 2013-01-07 18:03:37 from Simon Riggs

Responses

• Re: [PERFORM] Slow query: bitmap scan troubles at 2013-01-07 18:48:12 from Claudio Freire
• Re: [PERFORM] Slow query: bitmap scan troubles at 2013-01-11 01:07:34 from Tom Lane

pgsql-performance by date

Next:	From: ktm@rice.edu	Date: 2013-01-07 18:28:11
	Subject: Re: Sub optimal performance with default setting of Postgresql with FreeBSD 9.1 on ZFS
Previous:	From: nobody nowhere	Date: 2013-01-07 18:10:17
	Subject: Re: SMP on a heavy loaded database FIXED !!!!

pgsql-hackers by date

Next:	From: Claudio Freire	Date: 2013-01-07 18:48:12
	Subject: Re: [PERFORM] Slow query: bitmap scan troubles
Previous:	From: Simon Riggs	Date: 2013-01-07 18:03:37
	Subject: Re: [PERFORM] Slow query: bitmap scan troubles