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


--fTYva0sObOukM7+w
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Yeah, it's spending quite a lot of time finding the first matching row
> in each child table.  I'm curious why that is though; are the child
> tables not set up with nonoverlapping firstloadtime ranges?

They are set up w/ nonoverlapping firstloadtime ranges, using CHECK
constraints such as:

"osmoduleloads_2011_09_14_firstloadtime_check" CHECK (firstloadtime >=3D
129604464000000000::bigint::numeric AND firstloadtime <
129605328000000000::bigint::numeric)

The issue here is that the query is saying "Give me the first 150
records with this host_id in this week-long range".  PG happily
eliminates all the tables that are outside of the week-long range during
constraint exclusion.  After that, however, it hunts down the earliest
records (which matches 'host_id') from each child table.  Sure, from
each table there's a record in the week-long range with the host_id that
matches.  What PG doesn't realize is that it can stop after pulling the
150 records from the most recent table (and flipping the direction of
the query or the tables doesn't help- PG still pulls a record from each
table).

> > What would be great is if PG would realize that the CHECK constraints
> > prevent earlier records from being in these earlier tables,
>=20
> The explain shows that that isn't the case, because it *is* finding at
> least one candidate row in each table.  It's just running quite far into
> the firstloadtime sequence to do it.

My point above is that the CHECK constraints ensure an ordering which
could be leveraged to use the latest table first and then stop if enough
tuples are returned (or immediately go to the next table), without ever
considering the other tables.  I'm not looking for PG to eliminate those
other tables for consideration in all cases- if the limit is large
enough, it may get all the way down to them.  I'm pretty sure this isn't
something which PG does today and I don't expect teaching it to do this
to be trivial, but it certainly would be nice as this strikes me as a
very common use-case.

> If you're stuck with this table arrangement, one thing that would help
> is a two-column index on (host_guid, firstloadtime) on each child table.

Agreed, I mentioned this to the OP previously and it's on his list of
things to try.

	Thanks,
	=09
		Stephen

--fTYva0sObOukM7+w
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)

iEYEARECAAYFAk5/yJQACgkQrzgMPqB3kijJlgCfaucRDWB5OWwmwHS6MqdkRb2X
/WQAoJrP/idgWifMVq27P6lSbGE0P4yC
=YQHf
-----END PGP SIGNATURE-----

--fTYva0sObOukM7+w--