From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>
Cc: Greg Smith <greg@2ndQuadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query optimization using order by and limit
Date: Sun, 25 Sep 2011 21:24:05 -0400
Message-ID: <12134.1317000245@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sfrost@snowman.net, michael.viscuso@getcarbonblack.com, greg@2ndQuadrant.com
Subject: Re: Query optimization using order by and limit
In-Reply-To: <12134.1317000245@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox