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