From: Michael Viscuso <michael.viscuso@getcarbonblack.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Smith <greg@2ndQuadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query optimization using order by and limit
Date: Thu, 22 Sep 2011 18:55:47 -0400
Message-ID: <4E7BBCF3.4080502@getcarbonblack.com> (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>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Stephen,
I spent the better part of the day implementing an application layer
nested loop and it seems to be working well. Of course it's a little
slower than a Postgres only solution because it has to pass data back
and forth for each daily table query until it reaches the limit, but at
least I don't have "runaway" queries like I was seeing before. That
should be a pretty good stopgap solution for the time being.
I was really hoping there was a Postgres exclusive answer though! :) If
there are any other suggestions, it's a simple flag in my application to
query the other way again...
Thanks for all your help - and I'm still looking to change those
numerics to bigints, just haven't figured out the best way yet.
Mike
On 9/22/2011 10:53 AM, Stephen Frost wrote:
> * Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
>> Adding the final condition hosts_guid = '2007075705813916178' is what
>> ultimately kills it http://explain.depesz.com/s/8zy. By adding the
>> host_guid, it spends considerably more time in the older tables than
>> without this condition and I'm not sure why.
>
> 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. Having it as
> numerics isn't helping here, but the bigger issue is having to check all
> those tuples for a match to the filter.
>
> Mike, the filter has to be applied before the order by/limit, since
> those clauses come after the filter has been applied (you wouldn't want
> a 'where x = 2 limit 10' to return early just because it found 10
> records where x didn't equal 2).
>
> What would be great is if PG would realize that the CHECK constraints
> prevent earlier records from being in these earlier tables, so it
> shouldn't need to consider them at all once the records from the
> 'latest' table has been found and the limit reached (reverse all this
> for an 'ascending' query, of course), which we can do when there's no
> order by. I don't believe we have that kind of logic or that
> information available at this late stage- the CHECK constraints are used
> to eliminate the impossible-to-match tables, but that's it.
>
> One option, which isn't great of course, would be to implement your own
> 'nested loop' construct (something I typically despise..) in the
> application which just walks backwards from the latest and pulls
> whatever records it can from each day and then stops once it hits the
> limit.
>
> Thanks,
>
> Stephen
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iQEcBAEBAgAGBQJOe7zzAAoJEBKjVK2HR1IXYwAIAKQBnFOtCNljL1Hs1ZQW3e+I
ele/kZCiHzgHLFpN7zawt1Y7qf+3ntd6u+mkatJsnqeC+HY1Qee4VTUqr+hIKhcc
VIGuuYkzuojs6/PgF6MAERHP24lRFdLCQtMgTY8RshYODvc07VpqkLq1cXhsNJZw
6pNBTEpEmA0MzMrmk3x6C8lFbyXZAYUxNLwG5SEWecV+lkOjnA70oKnSxG6EXRgk
fkj2l1ezVn23KoO8SSUp4xBFHHOY/PQP9JtV7b52Gm5PC7lOqFFrXFygNP0KkWho
TzyjoYKttShEjmTMXoLt181+NB4rQEas8USasemRA1pUkx2NrfvcK46gYucOAsg=
=8yQW
-----END PGP SIGNATURE-----
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: michael.viscuso@getcarbonblack.com, sfrost@snowman.net, tgl@sss.pgh.pa.us, greg@2ndQuadrant.com
Subject: Re: Query optimization using order by and limit
In-Reply-To: <4E7BBCF3.4080502@getcarbonblack.com>
* 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