From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
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 20:34:28 -0400
Message-ID: <20110926003428.GS12765@tamriel.snowman.net> (raw)
In-Reply-To: <8338.1316989350@sss.pgh.pa.us>
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>
* 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?
They are set up w/ nonoverlapping firstloadtime ranges, using CHECK
constraints such as:
"osmoduleloads_2011_09_14_firstloadtime_check" CHECK (firstloadtime >=
129604464000000000::bigint::numeric AND firstloadtime <
129605328000000000::bigint::numeric)
The issue here is that the query is saying "Give me the first 150
records with this host_id in this week-long range". PG happily
eliminates all the tables that are outside of the week-long range during
constraint exclusion. After that, however, it hunts down the earliest
records (which matches 'host_id') from each child table. Sure, from
each table there's a record in the week-long range with the host_id that
matches. What PG doesn't realize is that it can stop after pulling the
150 records from the most recent table (and flipping the direction of
the query or the tables doesn't help- PG still pulls a record from each
table).
> > What would be great is if PG would realize that the CHECK constraints
> > prevent earlier records from being in these earlier tables,
>
> The explain shows that that isn't the case, because it *is* finding at
> least one candidate row in each table. It's just running quite far into
> the firstloadtime sequence to do it.
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. I'm not looking for PG to eliminate those
other tables for consideration in all cases- if the limit is large
enough, it may get all the way down to them. I'm pretty sure this isn't
something which PG does today and I don't expect teaching it to do this
to be trivial, but it certainly would be nice as this strikes me as a
very common use-case.
> 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.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
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: sfrost@snowman.net, tgl@sss.pgh.pa.us, michael.viscuso@getcarbonblack.com, greg@2ndQuadrant.com
Subject: Re: Query optimization using order by and limit
In-Reply-To: <20110926003428.GS12765@tamriel.snowman.net>
* 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