Date: Tue, 10 Jan 2006 22:10:55 -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: <20060110221055.053596ab@thunder.logicalchaos.org>
In-Reply-To: <20060110055818.GA55076@winnie.fuhr.org>
References: <20060109212338.6420af12@thunder.logicalchaos.org>
	<20060110055818.GA55076@winnie.fuhr.org>
Organization: Starlight Vision, LLC.
Mime-Version: 1.0
Content-Type: multipart/signed;
	boundary="Signature_Tue__10_Jan_2006_22_10_55_-0700_Gs9GVXt/AaVacPA+";
	protocol="application/pgp-signature"; micalg=pgp-sha1

--Signature_Tue__10_Jan_2006_22_10_55_-0700_Gs9GVXt/AaVacPA+
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable


Ok, I'm back, and in a little better shape.

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.

Taken individually:

weather=3D# explain analyze select * from doy_agg where doy =3D extract( do=
y from now() );
                                                               QUERY PLAN  =
                                                            =20
---------------------------------------------------------------------------=
-------------------------------------------------------------
 HashAggregate  (cost=3D13750.67..13750.71 rows=3D2 width=3D20) (actual tim=
e=3D123.134..123.135 rows=3D1 loops=3D1)
   ->  Bitmap Heap Scan on readings  (cost=3D25.87..13720.96 rows=3D3962 wi=
dth=3D20) (actual time=3D6.384..116.559 rows=3D4175 loops=3D1)
         Recheck Cond: (date_part('doy'::text, "when") =3D date_part('doy':=
:text, now()))
         ->  Bitmap Index Scan on readings_doy_index  (cost=3D0.00..25.87 r=
ows=3D3962 width=3D0) (actual time=3D5.282..5.282 rows=3D4215 loops=3D1)
               Index Cond: (date_part('doy'::text, "when") =3D date_part('d=
oy'::text, now()))
 Total runtime: 123.366 ms

produces the data:

weather=3D# select * from doy_agg where doy =3D extract( doy from now() );
 doy |  avg_windspeed   | max_windspeed=20
-----+------------------+---------------
  10 | 8.53403056583666 |            59

and:

weather=3D# EXPLAIN ANALYZE
weather-# SELECT *,
weather-# unmunge_time( time_group ) AS time
weather-# FROM minute."windspeed"
weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
weather-# ORDER BY time_group;
                                                                   QUERY PL=
AN                                                                   =20
---------------------------------------------------------------------------=
----------------------------------------------------------------------
 Sort  (cost=3D595.33..595.77 rows=3D176 width=3D28) (actual time=3D4.762..=
4.828 rows=3D283 loops=3D1)
   Sort Key: time_group
   ->  Bitmap Heap Scan on windspeed  (cost=3D2.62..588.76 rows=3D176 width=
=3D28) (actual time=3D0.901..3.834 rows=3D283 loops=3D1)
         Recheck Cond: (unmunge_time(time_group) > (now() - '24:00:00'::int=
erval))
         ->  Bitmap Index Scan on minute_windspeed_unmunge_index  (cost=3D0=
.00..2.62 rows=3D176 width=3D0) (actual time=3D0.745..0.745 rows=3D284 loop=
s=3D1)
               Index Cond: (unmunge_time(time_group) > (now() - '24:00:00':=
:interval))
 Total runtime: 5.108 ms

produces:

 time_group |    min_reading    | max_reading |    avg_reading    |        =
time        =20
------------+-------------------+-------------+-------------------+--------=
-------------
 1136869500 |               0.8 |           6 |  2.62193548387097 | 2006-01=
-09 22:05:00
 1136869800 |                 0 |           3 | 0.406021505376343 | 2006-01=
-09 22:10:00
 1136870100 |                 0 |           5 |              1.68 | 2006-01=
-09 22:15:00
...=20

But I want the composite of the two queries, and I'm stuck on:

weather=3D# EXPLAIN ANALYZE
weather-# SELECT *,
weather-# unmunge_time( time_group ) AS time
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 )
weather-# ORDER BY time_group;
                                                                         QU=
ERY PLAN                                                                   =
      =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------
 Sort  (cost=3D153627.67..153628.48 rows=3D322 width=3D48) (actual time=3D1=
0637.681..10637.748 rows=3D286 loops=3D1)
   Sort Key: windspeed.time_group
   ->  Merge Join  (cost=3D153604.82..153614.26 rows=3D322 width=3D48) (act=
ual time=3D10633.375..10636.728 rows=3D286 loops=3D1)
         Merge Cond: ("outer"."?column5?" =3D "inner".doy)
         ->  Sort  (cost=3D594.89..595.33 rows=3D176 width=3D28) (actual ti=
me=3D5.539..5.612 rows=3D286 loops=3D1)
               Sort Key: date_part('doy'::text, unmunge_time(windspeed.time=
_group))
               ->  Bitmap Heap Scan on windspeed  (cost=3D2.62..588.32 rows=
=3D176 width=3D28) (actual time=3D0.918..4.637 rows=3D286 loops=3D1)
                     Recheck Cond: (unmunge_time(time_group) > (now() - '24=
:00:00'::interval))
                     ->  Bitmap Index Scan on minute_windspeed_unmunge_inde=
x  (cost=3D0.00..2.62 rows=3D176 width=3D0) (actual time=3D0.739..0.739 row=
s=3D287 loops=3D1)
                           Index Cond: (unmunge_time(time_group) > (now() -=
 '24:00:00'::interval))
         ->  Sort  (cost=3D153009.93..153010.84 rows=3D366 width=3D20) (act=
ual time=3D10627.699..10627.788 rows=3D295 loops=3D1)
               Sort Key: doy_agg.doy
               ->  HashAggregate  (cost=3D152984.28..152990.69 rows=3D366 w=
idth=3D20) (actual time=3D10625.649..10626.601 rows=3D366 loops=3D1)
                     ->  Seq Scan on readings  (cost=3D0.00..145364.93 rows=
=3D1015914 width=3D20) (actual time=3D0.079..8901.123 rows=3D1015917 loops=
=3D1)
 Total runtime: 10638.298 ms

Where:

weather=3D# \d doy_agg
            View "public.doy_agg"
    Column     |       Type       | Modifiers=20
---------------+------------------+-----------
 doy           | double precision |=20
 avg_windspeed | double precision |=20
 max_windspeed | integer          |=20
View definition:
 SELECT doy_readings.doy, avg(doy_readings.windspeedaverage1) AS avg_windsp=
eed, max(doy_readings.windspeedmax1) AS max_windspeed
   FROM ONLY doy_readings
  GROUP BY doy_readings.doy;

which I don't want because of the full scan on readings.

I can easily do the two queries seperately in the script utilizing this dat=
a, but want to do it in the db itself.  I figure I'm just not seeing how to=
 combine the two queries effectively.

Thoughts?

Thanks,
Rob

--=20
 22:08:50 up 3 days, 14:35,  9 users,  load average: 2.71, 2.48, 2.51
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006

--Signature_Tue__10_Jan_2006_22_10_55_-0700_Gs9GVXt/AaVacPA+
Content-Type: application/pgp-signature

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

iEYEARECAAYFAkPEk3QACgkQLQ/DKuwDYznPUQCffh1APeMK0jSrKv03dgrm/25K
fhgAn0+2id9WEgmP0PfHzX5CVEFNbTyr
=kDMv
-----END PGP SIGNATURE-----

--Signature_Tue__10_Jan_2006_22_10_55_-0700_Gs9GVXt/AaVacPA+--