Re: why is index not used?

Hello,

I'm obviously new to Postgresql. Problem: I created simple table 'auth'
(with following code in Python) and also created an index, but when I
run a query, EXPLAIN ANALYZE says that sequential scan is done instead
of using an index.

Details:

import psycopg2

conn = psycopg2.connect("dbname=booktown user=postgres")
curs = conn.cursor()

curs.execute("""create table auth(first_name varchar(12), last_name
varchar(20), v1 float, v2 float, v3 int, v4 int, v5 varchar(50))""")

for x in range(97,97+26):
print chr(x)
for y in range(1,100000):
s="INSERT INTO auth VALUES ('%c%d" % (chr(x), y) + "', " +
"'%c%d'," % (chr(x), y) + "%d, %d, %d, %d, '%c%d')" % (y,y,y,y,chr(x),y)
#print s
curs.execute(s)

conn.commit()

SQL creation code for table:

create table auth(first_name varchar(12), last_name varchar(20), v1
float, v2 float, v3 int, v4 int, v5 varchar(50))

The Python code above fills first_name and last_name columns with values
like 'a1...'.

I also created index:

booktown=# create index first_name_idx on auth(first_name);

But now, when I do a select on that table, it does sequential scan
instead of using an index:

booktown=# explain analyze select * from auth where first_name like 'a11%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on auth (cost=0.00..56796.68 rows=1 width=42) (actual
time=0.091..983.665 rows=1111 loops=1)
Filter: ((first_name)::text ~~ 'a11%'::text)
Total runtime: 986.314 ms
(3 rows)

FAQ says that in order to use index, LIKE statements cannot begin with
%, so I should be fine?

Is there a way to make PostgreSQL use an index? Or is there smth I'm
missing before PGSQL uses an index to run this query?

Regards,
Marcin

On Tue, Oct 28, 2008 at 11:32 AM, Marcin Krol <mrkafk(at)gmail(dot)com> wrote:
> Hello,
>
> I'm obviously new to Postgresql. Problem: I created simple table 'auth'
> (with following code in Python) and also created an index, but when I run a
> query, EXPLAIN ANALYZE says that sequential scan is done instead of using an
> index.
>
> Details:
>
>
> import psycopg2
>
> conn = psycopg2.connect("dbname=booktown user=postgres")
> curs = conn.cursor()
>
> curs.execute("""create table auth(first_name varchar(12), last_name
> varchar(20), v1 float, v2 float, v3 int, v4 int, v5 varchar(50))""")
>
> for x in range(97,97+26):
> print chr(x)
> for y in range(1,100000):
> s="INSERT INTO auth VALUES ('%c%d" % (chr(x), y) + "', " + "'%c%d',"
> % (chr(x), y) + "%d, %d, %d, %d, '%c%d')" % (y,y,y,y,chr(x),y)
> #print s
> curs.execute(s)
>
> conn.commit()
>
>
>
>
> SQL creation code for table:
>
> create table auth(first_name varchar(12), last_name varchar(20), v1 float,
> v2 float, v3 int, v4 int, v5 varchar(50))
>
> The Python code above fills first_name and last_name columns with values
> like 'a1...'.
>
> I also created index:
>
> booktown=# create index first_name_idx on auth(first_name);
>
> But now, when I do a select on that table, it does sequential scan instead
> of using an index:
>
> booktown=# explain analyze select * from auth where first_name like 'a11%';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Seq Scan on auth (cost=0.00..56796.68 rows=1 width=42) (actual
> time=0.091..983.665 rows=1111 loops=1)
> Filter: ((first_name)::text ~~ 'a11%'::text)
> Total runtime: 986.314 ms
> (3 rows)
>
> FAQ says that in order to use index, LIKE statements cannot begin with %, so
> I should be fine?
>
> Is there a way to make PostgreSQL use an index? Or is there smth I'm missing
> before PGSQL uses an index to run this query?

You want to make sure that your table is analyzed before the index
will be useful. Given the discrepancy between the number of rows
expected and the number of rows returned, that may be the problem.

Sean

Marcin Krol <mrkafk(at)gmail(dot)com> writes:
> booktown=# explain analyze select * from auth where first_name like 'a11%';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Seq Scan on auth (cost=0.00..56796.68 rows=1 width=42) (actual
> time=0.091..983.665 rows=1111 loops=1)
> Filter: ((first_name)::text ~~ 'a11%'::text)
> Total runtime: 986.314 ms
> (3 rows)

> FAQ says that in order to use index, LIKE statements cannot begin with
> %, so I should be fine?

The other point is that in non-C locales, a standard varchar index isn't
usable for LIKE (the sort order is usually wrong). You can re-initdb
in C locale or create a varchar_pattern_ops index.

regards, tom lane

Tom Lane wrote:
> The other point is that in non-C locales, a standard varchar index isn't
> usable for LIKE (the sort order is usually wrong). You can re-initdb
> in C locale or create a varchar_pattern_ops index.

booktown=# create index first_name_idx_vpo on auth using btree
(first_name varchar_pattern_ops);

Yeehaw! Now it works. Thanks Tom!