Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Slow query: bitmap scan troubles

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PERFORM] Slow query: bitmap scan troubles
Date: 2013-01-07 18:27:38
Message-ID: 13842.1357583258@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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

Responses

pgsql-performance by date

Next:From: ktm@rice.eduDate: 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 nowhereDate: 2013-01-07 18:10:17
Subject: Re: SMP on a heavy loaded database FIXED !!!!

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group