Skip site navigation (1) Skip section navigation (2)

Postgres 8.1 choosing the larger index for an index scan

From: s anwar <sanwar(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Postgres 8.1 choosing the larger index for an index scan
Date: 2005-12-23 02:48:55
Message-ID: 3e3c86f90512221848g57ad71eeqd817a589246740a7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Reading the docs and the mailing lists, I had gathered that postgres favours
smaller indices during query optimization as compared to smaller ones.
However, I do not see that behaviour and would like to know if I am
misinterpreting how postgres processes the queries.

I have a very simple setup. I have one table with ~65000 tuples consuming
~8500 pages (reltuples and relpages as per pg_class). I have defined two
indices on table, t1_id1 which indexes "t1(id1)" which consumes 194 pages;
and t1_id1_v1 which indexes "t1(id1) where id1=9999" which consumes 4 pages.
When I run an "explain select * from t1 where id1=9999;" it chooses to
index-scan the t1_id1 index instead of t1_id1_v1 index, even though that the
where-clause uses an equal comparison and there is a smaller index defined
on t1 limited to the value of id1 to 9999.

My database is running on an x86_64 dual-processor platform with about 1G
given to postgres as shared memory and sequential-scan turned off. I have
already run an "analyze verbose" on the table.

Thanks.

Saadat.

Responses

pgsql-novice by date

Next:From: Ramon OrticioDate: 2005-12-23 02:52:49
Subject: 2 Questions
Previous:From: operationsengineer1Date: 2005-12-22 18:59:39
Subject: Re: Tsearch 2, index in different table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group