-----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@xxxxxxxxxxxxxxxxxx) 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----- -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
