Re: Improve Seq scan performance

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
Cc: Lutischán Ferenc <lutischanf(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Improve Seq scan performance
Date: 2008-11-10 08:41:03
Message-ID: 4917F39F.7080007@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Vladimir Sitnikov wrote:

> Suppose you want to find all the values that contain '%123%'. Currently
> PostgreSQL will do a sec scan, while the better option might be (and it is)
> to loop through all the items in the index (it will cost 30 I/O), find
> records that truly contain %123% (it will find 20 of them) and do 20 I/O to
> check tuple visiblity. That is 50 I/O versus 667 for seq scan.

That does make sense. The 20 visibility checks/tuple reads have a higher
cost than you've accounted for given that they require seeks. Assuming
Pg's random_page_cost assumption is right and that every tuple of
interest is on a different page it'd cost the equivalent of 80
sequential page reads, which still brings the total to only 110.

Anyway, sorry I've bothered you about this. I misunderstood the point
you were at in investigating this and hadn't realised you were very
familiar with Pg and its innards, so I tried to bring up some points
that might help someone who's facing typical issues like "why doesn't it
use an index for %thing%".

> Please, follow the case carefully: the index is only 30 pages long. Why is
> PostgreSQL doing 2529 I/O? It drives me crazy.

I certainly can't help you there, though I'm interested myself...

--
Craig Ringer

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-11-10 13:06:52 Re: Oddity with view
Previous Message Jim 'Decibel!' Nasby 2008-11-10 08:27:01 Oddity with view