Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 36F9C6342F3
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 20 May 2010 18:30:37 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 33123-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 20 May 2010 21:30:29 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51])
	by mail.postgresql.org (Postfix) with ESMTP id 60F0E634132
	for <pgsql-performance@postgresql.org>;
	Thu, 20 May 2010 18:30:30 -0300 (ADT)
Received: by tamriel.snowman.net (Postfix, from userid 1000)
	id 47BC822946; Thu, 20 May 2010 17:30:29 -0400 (EDT)
Date: Thu, 20 May 2010 17:30:29 -0400
From: Stephen Frost <sfrost@snowman.net>
To: David Jarvis <thangalin@gmail.com>
Cc: Matthew Wakeling <mnw21@cam.ac.uk>, pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or
	GIN?
Message-ID: <20100520213029.GQ21875@tamriel.snowman.net>
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
	<alpine.DEB.2.00.1005201355020.23334@aragorn.flymine.org>
	<AANLkTimaurzBzHWiir_Bd_GcljdE2IWOe1hF589sX49J@mail.gmail.com>
	<alpine.DEB.2.00.1005201259240.10164@aragorn.flymine.org>
	<AANLkTil9C0gclBGzm2XKPx74GwpZwffEHXoCxo63_7GY@mail.gmail.com>
	<alpine.DEB.2.00.1005201605170.23865@aragorn.flymine.org>
	<AANLkTik4bMwdV6C9Xyv1R1RfrV4OltRGwExGcdZZyEHI@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="a6h+skTJRhXEQ4I6"
Content-Disposition: inline
In-Reply-To: <AANLkTik4bMwdV6C9Xyv1R1RfrV4OltRGwExGcdZZyEHI@mail.gmail.com>
X-Editor: Vim http://www.vim.org/
X-Info: http://www.snowman.net
X-Operating-System: Linux/2.6.26-1-amd64 (x86_64)
X-Uptime: 17:26:06 up 266 days,  8:51, 33 users,  load average: 0.05, 0.10,
	0.08
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/131
X-Sequence-Number: 38715


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

* David Jarvis (thangalin@gmail.com) wrote:
> I was still referring to the measurement table. You have an index on
> > stationid, but still seem to be getting a sequential scan. Maybe the pl=
anner
> > does not realise that you are selecting a small number of stations. Pos=
ting
> > an EXPLAIN ANALYSE would really help here.
> >
>=20
> Here is the result from an *EXPLAIN ANALYZE*:

Yeah..  this is a horrible, horrible plan.  It does look like you've got
some serious data tho, at least.  Basically, PG is sequentially scanning
through all of the tables in your partitioning setup.  What is
constraint_exclusion set to?  What version of PG is this?  Do the
results og this query look at all correct to you?

Have you considered an index on elevation, btw?  How many records in
that city table are there and how many are actually in that range?

	Thanks,

		Stephen

--a6h+skTJRhXEQ4I6
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

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

iEYEARECAAYFAkv1qfUACgkQrzgMPqB3kigTMQCdEPx+A2fpBltwWk/bgm88DUkG
Uk0An3JPq85mYajNMZItFFpFxPybOnVt
=w7Vn
-----END PGP SIGNATURE-----

--a6h+skTJRhXEQ4I6--