From: Stephen Frost <sfrost@snowman.net>
To: Michael Viscuso <michael.viscuso@getcarbonblack.com>
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 19:14:56 -0400
Message-ID: <20110922231456.GN12765@tamriel.snowman.net> (raw)
In-Reply-To: <4E7BBCF3.4080502@getcarbonblack.com>
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>
<4E7BBCF3.4080502@getcarbonblack.com>
Mike,
* Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
> 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.
Glad to hear that you were able to get something going which worked for
you.
> 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...
I continue to wonder if some combination of multi-column indexes might
have made the task of finding the 'lowest' record from each of the
tables fast enough that it wouldn't be an issue.
> 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.
Our timestamps are also implemented using 64bit integers and would allow
you to use all the PG date/time functions and operators. Just a
thought.
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, michael.viscuso@getcarbonblack.com, tgl@sss.pgh.pa.us, greg@2ndQuadrant.com
Subject: Re: Query optimization using order by and limit
In-Reply-To: <20110922231456.GN12765@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