Date: Wed, 11 Jan 2006 07:26:59 -0700
From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Michael Fuhr <mike@fuhr.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Index isn't used during a join.
Message-ID: <20060111072659.7c1772ad@thunder.logicalchaos.org>
In-Reply-To: <20060111075655.GA51292@winnie.fuhr.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
	<20060110055818.GA55076@winnie.fuhr.org>
	<20060110221055.053596ab@thunder.logicalchaos.org>
	<20060111075655.GA51292@winnie.fuhr.org>
Organization: Starlight Vision, LLC.
Mime-Version: 1.0
Content-Type: multipart/signed;
	boundary=Signature_Wed__11_Jan_2006_07_26_59_-0700_P8qE46FONw9_i6Xw;
	protocol="application/pgp-signature"; micalg=pgp-sha1

--Signature_Wed__11_Jan_2006_07_26_59_-0700_P8qE46FONw9_i6Xw
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

When grilled further on (Wed, 11 Jan 2006 00:56:55 -0700),
Michael Fuhr <mike@fuhr.org> confessed:

> On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote:
> > The query is now correct, but still is slow because of lack of
> > index usage.  I don't know how to structure the query correctly to
> > use the index.
>=20
> Have you tried adding restrictions on doy in the WHERE clause?
> Something like this, I think:

I cannot.  That's what I thought I would get from the join.  The query show=
n will always have two days involved, and only grows from there.  The data =
is graphed at http://www.logicalchaos.org/weather/index.html, and I'm looki=
ng at adding historical data to the graphs.

Opps, never mind.  You hit the nail on the head:

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'::interval =
)=20
weather-# AND doy BETWEEN EXTRACT( doy FROM now() - '24 hour'::interval)=20
weather-#             AND EXTRACT( doy FROM now() )
weather-# ORDER BY time_group;
                                                                           =
                        QUERY PLAN                                         =
                                                         =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------------------------
 Sort  (cost=3D21914.09..21914.10 rows=3D1 width=3D48) (actual time=3D76.59=
5..76.662 rows=3D286 loops=3D1)
   Sort Key: windspeed.time_group
   ->  Hash Join  (cost=3D21648.19..21914.08 rows=3D1 width=3D48) (actual t=
ime=3D64.656..75.562 rows=3D286 loops=3D1)
         Hash Cond: (date_part('doy'::text, unmunge_time("outer".time_group=
)) =3D "inner".doy)
         ->  Bitmap Heap Scan on windspeed  (cost=3D2.27..267.40 rows=3D74 =
width=3D28) (actual time=3D0.585..1.111 rows=3D286 loops=3D1)
               Recheck Cond: (unmunge_time(time_group) > (now() - '24:00:00=
'::interval))
               ->  Bitmap Index Scan on minute_windspeed_unmunge_index  (co=
st=3D0.00..2.27 rows=3D74 width=3D0) (actual time=3D0.566..0.566 rows=3D287=
 loops=3D1)
                     Index Cond: (unmunge_time(time_group) > (now() - '24:0=
0:00'::interval))
         ->  Hash  (cost=3D21645.92..21645.92 rows=3D3 width=3D20) (actual =
time=3D63.849..63.849 rows=3D2 loops=3D1)
               ->  HashAggregate  (cost=3D21645.84..21645.89 rows=3D3 width=
=3D20) (actual time=3D63.832..63.834 rows=3D2 loops=3D1)
                     ->  Bitmap Heap Scan on readings  (cost=3D59.21..21596=
.85 rows=3D6532 width=3D20) (actual time=3D15.174..53.249 rows=3D7613 loops=
=3D1)
                           Recheck Cond: ((date_part('doy'::text, "when") >=
=3D date_part('doy'::text, (now() - '24:00:00'::interval))) AND (date_part(=
'doy'::text, "when") <=3D date_part('doy'::text, now())))
                           ->  Bitmap Index Scan on readings_doy_index  (co=
st=3D0.00..59.21 rows=3D6532 width=3D0) (actual time=3D12.509..12.509 rows=
=3D10530 loops=3D1)
                                 Index Cond: ((date_part('doy'::text, "when=
") >=3D date_part('doy'::text, (now() - '24:00:00'::interval))) AND (date_p=
art('doy'::text, "when") <=3D date_part('doy'::text, now())))
 Total runtime: 77.177 ms

What I had thought is that PG would (could?) be smart enough to realize tha=
t one query was restricted, and apply that restriction to the other based o=
n the join.  I know it works in other cases (using indexes on both tables u=
sing the join)...

>=20
> Something else occurred to me: do you (or will you) have more than
> one year of data?  If so then matching on doy could be problematic
> unless you also check for the year, or unless you want to match
> more than one year.

Yes and yes.  I'm doing both aggregate by day of the year for all data, and=
 aggregate by day of year within each year.  The examples are:

weather=3D# select * from doy_agg where doy =3D extract( doy from now() );
 doy |  avg_windspeed   | max_windspeed=20
-----+------------------+---------------
  11 | 6.14058239764748 |            69
(1 row)

weather=3D# select * from doy_day_agg where extract( doy from day ) =3D ext=
ract( doy from now() );
         day         |  avg_windspeed   | max_windspeed=20
---------------------+------------------+---------------
 2004-01-11 00:00:00 | 5.03991313397539 |            17
 2006-01-11 00:00:00 |  18.532050716667 |            69
 2005-01-11 00:00:00 |  3.6106763448041 |            13

Thanks for your help Michael.

Cheers,
Rob

--=20
 07:07:30 up 3 days, 23:34,  9 users,  load average: 2.29, 2.44, 2.43
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006

--Signature_Wed__11_Jan_2006_07_26_59_-0700_P8qE46FONw9_i6Xw
Content-Type: application/pgp-signature

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

iEYEARECAAYFAkPFFbMACgkQLQ/DKuwDYzk7EACfenPC//X9+XTTW4KD2cTWmysA
r18AoJBs9KI0bOW0HikfbPucsF79Wjut
=ogeI
-----END PGP SIGNATURE-----

--Signature_Wed__11_Jan_2006_07_26_59_-0700_P8qE46FONw9_i6Xw--