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 18:22:30 -0400
Message-ID: <8338.1316989350@sss.pgh.pa.us> (raw)
In-Reply-To: <20110922145319.GJ12765@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>
Stephen Frost <sfrost@snowman.net> writes:
> What I think is happening here is that PG is pushing down that filter
> (not typically a bad thing..), but with that condition, it's going to
> scan the index until it finds a match for that filter before returning
> back up only to have that result cut out due to the limit.
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?
> 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.
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.
That would match the search condition exactly, and so reduce the cost
to find the first matching row to nearly nil. Whether this query's
speed is important enough to justify maintaining such an index is a
question I can't answer for you.
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: <8338.1316989350@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