Re: Creating tons of tables to support a query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: S Dawalt <shane(dot)dawalt(at)wright(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Creating tons of tables to support a query
Date: 2002-09-09 20:37:07
Message-ID: 13556.1031603827@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

S Dawalt <shane(dot)dawalt(at)wright(dot)edu> writes:
> select msgnum from message where
> sectionID = ? and
> dateSent > ?
> order by dateSent
> limit 1;
>>
>> I don't think that'll use an index on (sectionID, dateSent) for the sort
>> step. I think an index on (dateSent,sectionID) might be, however.

> I know I've read this before on the list (probably several times). But
> either my skull is too thick or the topic too abstract; why is no index used
> for (sectionID, dateSent) but (dateSent, sectionID) does?

The issue is whether the indexscan satisfies the ORDER BY condition or
just the WHERE conditions. If the planner thinks it needs both an
indexscan and a subsequent SORT step, it is much less likely to choose
the indexscan-based plan --- and rightfully so in this case, since the
LIMIT doesn't help if you have to sort the data before you know which
is the single output row you should return. That is,
LIMIT
INDEXSCAN
can be a very cheap plan, but
LIMIT
SORT
INDEXSCAN
is not likely to be cheap, because the LIMIT helps not at all for
aborting the indexscan or the sort short of completion.

Now, you know and I know that given the constraint "WHERE sectionID = ?"
it would actually be okay to pretend that indexscanning an index on
(sectionID, dateSent) yields data ordered simply by dateSent. The
planner will not currently make that deduction, however, and so you have
to help it along by asking for your data "ORDERED BY sectionID,
dateSent". The system is able to match that to the sort ordering of the
two-column index and realize that it needs no SORT step.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-09-09 20:47:20 Re: pg_restore not able to restore files larger that 2.4GB
Previous Message Johnson, Shaunn 2002-09-09 20:22:08 Re: Load sharing question