To: Stephen Frost <sfrost@snowman.net>
cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>,
	Greg Smith <greg@2ndQuadrant.com>, pgsql-performance@postgresql.org
Subject: Re: Query optimization using order by and limit 
In-reply-to: <20110926003428.GS12765@tamriel.snowman.net> 
References: 
 <CA+Z27QQDRQ1h_tD1Tm_+T+-cBdt00hLWYRnvXygxLf_JWNTxZQ@mail.gmail.com>
	<4E7A93F9.1050301@2ndQuadrant.com> <6768.1316657361@sss.pgh.pa.us>
	<4E7AA399.6000209@getcarbonblack.com>
	<20110922145319.GJ12765@tamriel.snowman.net>
	<8338.1316989350@sss.pgh.pa.us>
	<20110926003428.GS12765@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Sun, 25 Sep 2011 20:34:28 -0400"
Date: Sun, 25 Sep 2011 21:24:05 -0400
Message-ID: <12134.1317000245@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.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