From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Lutischán Ferenc <lutischanf@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve Seq scan performance
Date: Mon, 10 Nov 2008 00:18:12 -0800
Message-ID: <1d709ecc0811100018o5058377bl8fd7b2ddd7cba96a@mail.gmail.com> (raw)
In-Reply-To: <4917E9F1.5080902@postnewspapers.com.au>
References: <4917D9C3.9060001@gmail.com>
<4917DAFB.8000003@postnewspapers.com.au>
<1d709ecc0811092337q74fee9e7vc418eba9c798f3a4@mail.gmail.com>
<4917E9F1.5080902@postnewspapers.com.au>
>
> Maybe there's some hybrid type possible where you can scan the index to
> find large table regions that are known /not/ to contain tuples of interest
> and seek over them in your scan. I wouldn't know, really, but it sounds like
> it'd probably be more I/O than a pure seq scan (given the reading of the
> index too) unless the table had the values of interest rather neatly
> clustered. It'd also surely use more memory and CPU time processing the
> whole index to find table regions without values of interest.
>
> Is that what you meant, though?
Not exactly. I mean the following: there are cases when index scan even
over non-clustered values is a complete win (basically, it is a win when the
number of returned values is relatively small no matter is it due to
selectivity or due to limit clause).
The test case that I have provided creates a 667 pages long table and 30
pages long index thus a complete scan of the index is 22 times faster in
terms of I/O.
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.
> A b-tree index cannot be used on a LIKE query with a leading wildcard. See
> the FAQ.
Unfortunately it is true. I would love to improve that particular case.
In addition, if your database is not in the C locale you can't use an
> ordinary index for LIKE queries. See the FAQ. You need to create a
> text_pattern_ops index instead:
>
> create index i_ix_txt on seq_test(i text_pattern_ops);
Good catch. However, that does not change the results. PostgresSQL does the
same amount of 2529 I/O for index scan on '%123%' for some unknown reason.
>
>
> set enable_seqscan=off
>> -- Index Scan reads 2529 pages for some reason. I would expect *30 *(index
>> size) + *20 *(number of matching entries) = 50 pages maximum, that is 10
>> times better than with seq scan.
>> Index Scan using i_ix on seq_test (cost=0.00..1643.74 rows=356 width=508)
>> (actual time=0.334..16.746 rows=*20 *loops=1 read_shared=2529(2529)
>> read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0)
>> Filter: (i ~~ '%123%'::text)
>> Total runtime: 16.863 ms
>>
>
> I think it's reading the whole index, because it can't do a prefix search
> if there's a leading wildcard. I'm a bit confused, though, since I thought
> in this case it couldn't actually execute the query w/o a sequential scan,
> and would just use one irrespective of the enable_seqscan param. That's what
> happens here.
Please, follow the case carefully: the index is only 30 pages long. Why is
PostgreSQL doing 2529 I/O? It drives me crazy.
Regards,
Vladimir Sitnikov
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: sitnikov.vladimir@gmail.com, craig@postnewspapers.com.au, lutischanf@gmail.com
Subject: Re: Improve Seq scan performance
In-Reply-To: <1d709ecc0811100018o5058377bl8fd7b2ddd7cba96a@mail.gmail.com>
* 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