How does the planner deal with multiple possible indexes?

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: How does the planner deal with multiple possible indexes?
Date: 2006-07-19 21:58:00
Message-ID: 20060719215800.GC83250@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Client has a table with 9 indexes; one is on just ident_id and takes up
75181 pages, the other is on ident_id and another field and uses 117461
pages.

"bdata__ident_filed_departure" btree (ident_id, filed_departuretime), tablespace "array4"
"bdata_ident" btree (ident_id), tablespace "array4"

Whats interesting is that even a simple

SELECT * FROM table WHERE ident_id=1234

uses bdata__ident_filled_departure, even though it would require less IO
to use bdata_ident.

\d does list bdata__ident_filed_departure before bdata_ident; I'm
wondering if the planner is finding the first index with ident_id in it
and stopping there?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joe Conway 2006-07-19 22:06:01 Re: pgxs problem
Previous Message Michael Fuhr 2006-07-19 21:57:31 Re: pgxs problem