Re: Improve Seq scan performance - Mailing list pgsql-performance

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?

A b-tree index cannot be used on a LIKE query with a leading wildcard. See the FAQ.

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);

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.