Re: Query optimization using order by and limit

Stephen,

Yes, I couldn't agree more. The next two things I will be looking at very
carefully are the timestamps and indexes. I will reply to this post if
either dramatically helps.

Thanks again for all your help. My eyes were starting to bleed from staring
at explain logs!

Mike

On Thu, Sep 22, 2011 at 7:14 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Mike,
>
> * Michael Viscuso (michael(dot)viscuso(at)getcarbonblack(dot)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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk57wXAACgkQrzgMPqB3kijaNwCfQ9cSdzzHyiPwa+BTzIihWR7T
> baoAoIbL8P3atU1cfbcCoFXFGbKE7fPt
> =ZRqu
> -----END PGP SIGNATURE-----
>
>