why is index not used?

From: Marcin Krol <mrkafk(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: why is index not used?
Date: 2008-10-28 15:32:17
Message-ID: 49073081.3050604@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2008-10-28 16:11:46 Re: why is index not used?
Previous Message Bruce Hyatt 2008-10-27 17:31:57 Re: combining tables