Date: Thu, 22 Sep 2011 19:14:56 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Michael Viscuso <michael.viscuso@getcarbonblack.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Greg Smith <greg@2ndQuadrant.com>,
	pgsql-performance@postgresql.org
Subject: Re: Query optimization using order by and limit
Message-ID: <20110922231456.GN12765@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>
	<4E7BBCF3.4080502@getcarbonblack.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="8KlXS1coelMGD729"
Content-Disposition: inline
In-Reply-To: <4E7BBCF3.4080502@getcarbonblack.com>
User-Agent: Mutt/1.5.20 (2009-06-14)


--8KlXS1coelMGD729
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--8KlXS1coelMGD729
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEARECAAYFAk57wXAACgkQrzgMPqB3kijaNwCfQ9cSdzzHyiPwa+BTzIihWR7T
baoAoIbL8P3atU1cfbcCoFXFGbKE7fPt
=ZRqu
-----END PGP SIGNATURE-----

--8KlXS1coelMGD729--