Re: A plan returned by explain doesn't make sense to me

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: nickf(at)ontko(dot)com
Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: A plan returned by explain doesn't make sense to me
Date: 2002-04-05 16:45:36
Message-ID: 24798.1018025136@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
>> That seems strange to me also, particularly if the index column ordering
>> is indeed actor_id,case_id and not the other way round

> Actually, it *is* the other way around- I didn't realize that could make a
> difference.

Ah. Okay, that eliminates the apparent impossibility.

> I think this solves my immediate problem, but it seems like even with the
> reversed order, the planner shouldn't have chosen the combined index to
> drive the query, so I'm happy for now, but I fear that I've added a task to
> someone's list in the developer's enclave.

The only reason the planner should choose a single-column index over
using the first column of a multi-column index is that the latter index
is likely to be physically larger and thus require more I/O to access.
So, there's no penalty in the cost calculations other than the
number-of-blocks-of-I/O estimated from the physical index size. It
would be interesting to see the reltuples and relpages stats from
pg_class for your single- and multi-column indexes. (However, it may be
too late now since you blew away and rebuilt the multi-column index;
we can't be entirely sure what stats the planner was seeing before.)

It's actually a standard recommendation that you not bother with an
index on a single column x if you also have one on (x,y). Unless the
table is almost entirely read-only, the cost of updating two indexes
outweighs the I/O savings of using the smaller index for queries that
use only x.

regards, tom lane

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Marin Dimitrov 2002-04-05 17:01:13 Re: Data Files
Previous Message Nick Fankhauser 2002-04-05 16:27:30 Re: A plan returned by explain doesn't make sense to me