Re: Creating tons of tables to support a query

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