Re: Creating tons of tables to support a query

Jan Ploski <jpljpl(at)gmx(dot)de> writes:
> On Sun, Sep 08, 2002 at 07:49:32PM -0700, Stephan Szabo wrote:
>> On Sun, 8 Sep 2002, Jan Ploski wrote:
>>> I am in particular wondering, why an index on message(sectionID, dateSent)
>>> does not make these queries comparably fast:
>>>
>>> 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.

> Alas, this does not help me further. I did two tests:

Yes, it makes sense that for a little-used section that way wouldn't be
very efficient. I would suggest that you want to use an index on
(sectionID, dateSent), and that the way to make the system do the
right thing is

select msgnum from message where
sectionID = ? and
dateSent > ?
order by sectionID, dateSent
limit 1;

Without the extra ORDER BY clause, the planner is not smart enough to
see that the requested ordering actually matches the index ordering.

Another possible gotcha is that depending on datatype details the
planner might be using only one of the two index columns. As far
as I noticed, you didn't tell us the exact column datatypes or the
exact form in which the comparison values are supplied?

regards, tom lane