select like and indexes

Started by William N. Zanattaabout 23 years ago2 messagesgeneral
Jump to latest
#1William N. Zanatta
william@veritel.com.br

Hi,

I'm quite new to postgresql. I need some help to understand some of
'how it works'.

I have a table with aprox. 1 million tuples (row == tuple ??) and
I've created an index on a column 'ip' in my table.

Well the question is: when I do,

explain analyze select * from tbl_xxxx where ip like '200%';

why PG doesn't use Index Scan method? why does it uses sequential
scan in place?

Thank you,

william

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: William N. Zanatta (#1)
Re: select like and indexes

I'm quite new to postgresql. I need some help to understand some of
'how it works'.

I have a table with aprox. 1 million tuples (row == tuple ??) and
I've created an index on a column 'ip' in my table.

Well the question is: when I do,

explain analyze select * from tbl_xxxx where ip like '200%';

why PG doesn't use Index Scan method? why does it uses sequential
scan in place?

If you aren't in the "C" locale, you won't get index scans on like
queries at all. Recent initdbs should warn you of this.

The problem is that the conversion of the above into the currently used
indexable form (something like ip>='200' and ip<'201') doesn't necessarily
work in every locale. There's been talk at various times about this, but
I don't think anything was decided.