Re: why is index not used?

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