X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B17E49DC845 for ; Wed, 11 Jan 2006 11:02:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94782-05 for ; Wed, 11 Jan 2006 11:02:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from rambo.iniquinet.com (rambo.iniquinet.com [69.39.89.10]) by postgresql.org (Postfix) with ESMTP id 099889DC833 for ; Wed, 11 Jan 2006 11:02:35 -0400 (AST) Received: (qmail 6101 invoked by uid 1010); 11 Jan 2006 10:02:40 -0500 Received: from Robert_Creager@LogicalChaos.org by rambo.iniquinet.com by uid 1002 with qmail-scanner-1.20 (spamassassin: 3.1.0. Clear:RC:0(63.147.78.66):SA:0(?/?):. Processed in 0.747927 secs); 11 Jan 2006 15:02:40 -0000 Received: from unknown (HELO thunder.logicalchaos.org) (perl?test@logicalchaos.org@63.147.78.66) by rambo.iniquinet.com with AES256-SHA encrypted SMTP; 11 Jan 2006 10:02:40 -0500 Received: from logicalchaos.org (localhost [127.0.0.1]) by thunder.logicalchaos.org (Postfix) with ESMTP id 0377C333F1; Wed, 11 Jan 2006 08:02:37 -0700 (MST) Date: Wed, 11 Jan 2006 08:02:37 -0700 From: Robert Creager Cc: Michael Fuhr , PGPerformance Subject: Re: Index isn't used during a join. Message-ID: <20060111080237.66040a61@thunder.logicalchaos.org> In-Reply-To: <20060111072659.7c1772ad@thunder.logicalchaos.org> References: <20060109212338.6420af12@thunder.logicalchaos.org> <20060110055818.GA55076@winnie.fuhr.org> <20060110221055.053596ab@thunder.logicalchaos.org> <20060111075655.GA51292@winnie.fuhr.org> <20060111072659.7c1772ad@thunder.logicalchaos.org> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed-Claws 1.0.4 (GTK+ 1.2.10; i586-mandriva-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; boundary="Signature_Wed__11_Jan_2006_08_02_37_-0700_RiKYkfFcX0Sf=Pvx"; protocol="application/pgp-signature"; micalg=pgp-sha1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.184 required=5 tests=[AWL=-0.005, MISSING_HEADERS=0.189] X-Spam-Score: 0.184 X-Spam-Level: X-Archive-Number: 200601/111 X-Sequence-Number: 16589 --Signature_Wed__11_Jan_2006_08_02_37_-0700_RiKYkfFcX0Sf=Pvx Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable When grilled further on (Wed, 11 Jan 2006 07:26:59 -0700), Robert Creager confessed: >=20 > weather-# SELECT *, unmunge_time( time_group ) AS time, > weather-# EXTRACT( doy FROM unmunge_time( time_group ) ) > weather-# FROM minute."windspeed" > weather-# JOIN doy_agg ON( EXTRACT( doy FROM unmunge_time( time_group ) )= =3D doy ) > weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interva= l )=20 > weather-# AND doy BETWEEN EXTRACT( doy FROM now() - '24 hour'::interval)= =20 > weather-# AND EXTRACT( doy FROM now() ) > weather-# ORDER BY time_group; The more I think about it, the more I believe PG is missing an opportunity.= The query is adequately constrained without the BETWEEN clause. Why does= n't PG see that? I realize I'm a hack and by db organization shows that... The query is wrong as stated, as it won't work when the interval crosses a = year boundary, but it's a stop gap for now. Cheers, Rob --=20 07:58:30 up 4 days, 25 min, 9 users, load average: 2.13, 2.15, 2.22 Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006 --Signature_Wed__11_Jan_2006_08_02_37_-0700_RiKYkfFcX0Sf=Pvx Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) iEYEARECAAYFAkPFHg0ACgkQLQ/DKuwDYzkn0gCglCZYhYZDuIuf7pjSpF6booRy sRcAnilZXRfJtmchUxcoZkZogXmGawc4 =QodM -----END PGP SIGNATURE----- --Signature_Wed__11_Jan_2006_08_02_37_-0700_RiKYkfFcX0Sf=Pvx--