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
