Thread: Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

>> We've seen a lot of those lately -- Index Scan Backward
>> performing far worse than alternatives.
>
> It's not clear to me that that has anything to do with Tim's
> problem.  It certainly wouldn't be 20000x faster if it were a
> forward scan.

Well, that's one way of looking at it.  Another would be that the
slower plan with the backward scan was only estimated to be 14.5%
less expensive than the fast plan, so a pretty moderate modifier
would have avoided this particular problem.  The fact that the
backward scan mis-estimate may be combining multiplicatively with
other mis-estimates doesn't make it less important.

-Kevin

On 03/16/2011 12:44 PM, Kevin Grittner wrote:

> Well, that's one way of looking at it.  Another would be that the
> slower plan with the backward scan was only estimated to be 14.5%
> less expensive than the fast plan, so a pretty moderate modifier
> would have avoided this particular problem.

I was wondering about that myself. Considering any backwards scan would
necessarily be 10-100x slower than a forward scan unless the data was on
an SSD, I assumed the planner was already using a multiplier to
discourage its use.

If not, it seems like a valid configurable. We set our random_page_cost
to 1.5 once the DB was backed by NVRAM. I could see that somehow
influencing precedence of a backwards index scan. But even then, SSDs
and their ilk react more like RAM than even a large RAID... so should
there be a setting that passes such useful info to the planner?

Maybe a good attribute to associate with the tablespace, if nothing else.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

On Wed, Mar 16, 2011 at 3:34 PM, Shaun Thomas <sthomas@peak6.com> wrote:
> If not, it seems like a valid configurable. We set our random_page_cost to
> 1.5 once the DB was backed by NVRAM. I could see that somehow influencing
> precedence of a backwards index scan. But even then, SSDs and their ilk
> react more like RAM than even a large RAID... so should there be a setting
> that passes such useful info to the planner?

Forgive the naive question...
but...

Aren't all index scans, forward or backward, random IO?

Claudio Freire <klaussfreire@gmail.com> wrote:

> Forgive the naive question...
> but...
>
> Aren't all index scans, forward or backward, random IO?

No.  Some could approach that; but, for example, an index scan
immediately following a CLUSTER on the index would be totally
sequential on the heap file access and would tend to be fairly close
to sequential on the index itself.  It would certainly trigger OS
level read-ahead for the heap, and quite possibly for the index.  So
for a lot of pages, the difference might be between copying a page
from the OS cache to the database cache versus a random disk seek.

To a lesser degree than CLUSTER you could get some degree of
sequencing from a bulk load or even from normal data insert
patterns.  Consider a primary key which is sequentially assigned, or
a timestamp column, or receipt numbers, etc.

As Tom points out, some usage patterns may scramble this natural
order pretty quickly.  Some won't.

-Kevin