From: Craig Ringer <craig@postnewspapers.com.au>
To: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Cc: Lutischán Ferenc <lutischanf@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve Seq scan performance
Date: Mon, 10 Nov 2008 17:41:03 +0900
Message-ID: <4917F39F.7080007@postnewspapers.com.au> (raw)
In-Reply-To: <1d709ecc0811100018o5058377bl8fd7b2ddd7cba96a@mail.gmail.com>
References: <4917D9C3.9060001@gmail.com>
<4917DAFB.8000003@postnewspapers.com.au>
<1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
<4917E9F1.5080902@postnewspapers.com.au>
<1d709ecc0811100018o5058377bl8fd7b2ddd7cba96a@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, sitnikov.vladimir@gmail.com, lutischanf@gmail.com
Subject: Re: Improve Seq scan performance
In-Reply-To: <4917F39F.7080007@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox