Обсуждение: Postgres 8.1 choosing the larger index for an index scan
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.
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.
s anwar <sanwar@gmail.com> writes: > 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=3D9999" which consumes 4 pag= > es. > When I run an "explain select * from t1 where id1=3D9999;" it chooses to > index-scan the t1_id1 index instead of t1_id1_v1 index, even though that th= > e > where-clause uses an equal comparison and there is a smaller index defined > on t1 limited to the value of id1 to 9999. What's the datatype of id1? There are some limitations in the current code about recognizing cross-type WHERE conditions ... regards, tom lane
id1 is of integer type.
On 12/22/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
s anwar <sanwar@gmail.com> writes:
> 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=3D9999" which consumes 4 pag=
> es.
> When I run an "explain select * from t1 where id1=3D9999;" it chooses to
> index-scan the t1_id1 index instead of t1_id1_v1 index, even though that th=
> e
> where-clause uses an equal comparison and there is a smaller index defined
> on t1 limited to the value of id1 to 9999.
What's the datatype of id1? There are some limitations in the current
code about recognizing cross-type WHERE conditions ...
regards, tom lane