Re: Query optimization using order by and limit

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Yeah, it's spending quite a lot of time finding the first matching row
>> in each child table. I'm curious why that is though; are the child
>> tables not set up with nonoverlapping firstloadtime ranges?

> The issue here is that the query is saying "Give me the first 150
> records with this host_id in this week-long range".

Oh, I see. So the query range overlaps multiple child tables, even
after constraint exclusion eliminates a lot of them.

> My point above is that the CHECK constraints ensure an ordering which
> could be leveraged to use the latest table first and then stop if enough
> tuples are returned (or immediately go to the next table), without ever
> considering the other tables.

Yeah. My opinion is that trying to reverse-engineer that from the CHECK
constraints would cost a lot more than it's worth. What we need, and
will hopefully have sooner or later, is an abstract concept of
"partitioned table" in which this kind of relationship is known a-priori
instead of having to be laboriously re-deduced every time we plan a
query.

>> If you're stuck with this table arrangement, one thing that would help
>> is a two-column index on (host_guid, firstloadtime) on each child table.

> Agreed, I mentioned this to the OP previously and it's on his list of
> things to try.

AFAICS the fact that this example would be fast if we were only paying
attention to the newest table is mere luck. If it can take a long time
to find the first matching host_guid record in several of the child
tables, why might it not take just as long to find said record in the
other one? I think you really need the two-column indexes, if keeping
this query's runtime to a minimum is critical.

regards, tom lane