How does the planner deal with multiple possible indexes?
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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On 7/19/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
[snip]
\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?
From my own experience it was grabbing the first that has the
requested field as its first member.. I haven't looked at the code to
see if that is the intended behavior.
"Gregory Maxwell" <gmaxwell@gmail.com> writes:
On 7/19/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
[snip]\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?
From my own experience it was grabbing the first that has the
requested field as its first member.. I haven't looked at the code to
see if that is the intended behavior.
Ordering would only matter if the estimated costs were exactly the same,
which they probably shouldn't be for indexes with such different sizes.
However, if the estimated number of matching rows were real small, you
might be winding up with a "one page to fetch" estimate in either case.
Jim didn't provide enough details to guess what the cost estimates
actually are...
regards, tom lane
On Wed, Jul 19, 2006 at 07:00:40PM -0400, Tom Lane wrote:
"Gregory Maxwell" <gmaxwell@gmail.com> writes:
On 7/19/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
[snip]\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?From my own experience it was grabbing the first that has the
requested field as its first member.. I haven't looked at the code to
see if that is the intended behavior.Ordering would only matter if the estimated costs were exactly the same,
which they probably shouldn't be for indexes with such different sizes.
However, if the estimated number of matching rows were real small, you
might be winding up with a "one page to fetch" estimate in either case.
Jim didn't provide enough details to guess what the cost estimates
actually are...
Indeed, if I find a case where there's a large enough number of rows it
will choose the smaller index. But I'm wondering if it would be better
to always favor the smaller index, since it would (presumably) be easier
to keep it in cache?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
Indeed, if I find a case where there's a large enough number of rows it
will choose the smaller index. But I'm wondering if it would be better
to always favor the smaller index, since it would (presumably) be easier
to keep it in cache?
AFAICS, in existing releases that should happen, because the cost
estimate varies with the size of the index. And it does happen for me
in simple tests. You did not provide the requested information to help
us find out why it's not happening for you.
(I'm a bit worried about whether CVS HEAD may have broken this behavior
with the recent changes in the indexscan cost equations ... but unless
you are working with HEAD that's not relevant.)
regards, tom lane
On Wed, Jul 19, 2006 at 07:54:49PM -0400, Tom Lane wrote:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
Indeed, if I find a case where there's a large enough number of rows it
will choose the smaller index. But I'm wondering if it would be better
to always favor the smaller index, since it would (presumably) be easier
to keep it in cache?AFAICS, in existing releases that should happen, because the cost
estimate varies with the size of the index. And it does happen for me
in simple tests. You did not provide the requested information to help
us find out why it's not happening for you.(I'm a bit worried about whether CVS HEAD may have broken this behavior
with the recent changes in the indexscan cost equations ... but unless
you are working with HEAD that's not relevant.)
No, this is 8.1.3, and it's a production machine so I'd prefer not to go
about dropping indexes to get cost comparisons; unless there's some way
to disable the use of an index in a given backend? Otherwise I'll try
and come up with a test case.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, R, 2006-07-21 kell 08:29, kirjutas Jim C. Nasby:
No, this is 8.1.3, and it's a production machine so I'd prefer not to go
about dropping indexes to get cost comparisons; unless there's some way
to disable the use of an index in a given backend?
Currently the closest thing is
BEGIN;
DROP INDEX xxx;
test query here
ABORT;
Otherwise I'll try
and come up with a test case.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
"Jim C. Nasby" <jnasby@pervasive.com> writes:
No, this is 8.1.3, and it's a production machine so I'd prefer not to go
about dropping indexes to get cost comparisons; unless there's some way
to disable the use of an index in a given backend?
The traditional hack for that is
begin;
drop index foo;
explain whatever;
rollback;
The DROP acquires an exclusive lock on the table, but it's only held for
a very short time while you EXPLAIN (you might want to put the whole
thing in a script file instead of relying on human typing speed). So
unless you've got seriously strict response time requirements, this is
generally OK even in production DBs. You do have to watch out for long
running transactions holding non-exclusive locks, eg don't try this
while a VACUUM is running on the table --- else the DROP blocks on the
vacuum and all other accesses start to queue up behind the DROP.
If the online-index-build patch gets in, there will be a cleaner option
which is to just mark the index disabled in pg_index. That doesn't
require any exclusive lock, indeed won't be visible to other backends at
all if you do it within a transaction as above.
regards, tom lane