Index isn't used during a join. - Mailing list pgsql-performance

Hey folks,

I'm working with a query to get more info out with a join.  The base query works great speed wise because of index
usage. When the join is tossed in, the index is no longer used, so the query performance tanks. 

Can anyone advise on how to get the index usage back?

weather=# select version();
                                                        version
-----------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1 (4.0.1-5mdk for Mandriva Linux release 2006.0)
(1 row)

The base query is:

weather=# EXPLAIN ANALYZE
weather-# SELECT min_reading, max_reading, avg_reading, -- doy,
weather-#        unmunge_time( time_group ) AS time
weather-# FROM minute."windspeed"
weather-# --JOIN readings_doy ON EXTRACT( doy FROM unmunge_time( time_group ) ) = doy
weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
weather-# ORDER BY time_group;
                                                                 QUERY PLAN
                     

---------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10995.29..11155.58 rows=64117 width=28) (actual time=4.509..4.574 rows=285 loops=1)
   Sort Key: time_group
   ->  Bitmap Heap Scan on windspeed  (cost=402.42..5876.05 rows=64117 width=28) (actual time=0.784..3.639 rows=285
loops=1)
         Recheck Cond: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
         ->  Bitmap Index Scan on minute_windspeed_index  (cost=0.00..402.42 rows=64117 width=0) (actual
time=0.675..0.675rows=285 loops=1) 
               Index Cond: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
 Total runtime: 4.880 ms
(7 rows)

When I add in the join, the query tosses out the nice quick index in favor of sequence scans:

weather=# EXPLAIN ANALYZE
weather-# SELECT min_reading, max_reading, avg_reading, -- doy,
weather-#        unmunge_time( time_group ) AS time
weather-# FROM minute."windspeed"
weather-# JOIN readings_doy ON EXTRACT( doy FROM unmunge_time( time_group ) ) = doy
weather-# WHERE unmunge_time( time_group ) > ( now() - '24 hour'::interval )
weather-# ORDER BY time_group;
                                                               QUERY PLAN
                 

-----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=98239590.88..99052623.66 rows=325213113 width=28) (actual time=60136.484..61079.845 rows=1030656 loops=1)
   Sort Key: windspeed.time_group
   ->  Merge Join  (cost=265774.21..8396903.54 rows=325213113 width=28) (actual time=34318.334..47113.277 rows=1030656
loops=1)
         Merge Cond: ("outer"."?column5?" = "inner"."?column2?")
         ->  Sort  (cost=12997.68..13157.98 rows=64120 width=28) (actual time=2286.155..2286.450 rows=284 loops=1)
               Sort Key: date_part('doy'::text, unmunge_time(windspeed.time_group))
               ->  Seq Scan on windspeed  (cost=0.00..7878.18 rows=64120 width=28) (actual time=2279.275..2285.271
rows=284loops=1) 
                     Filter: (unmunge_time(time_group) > (now() - '24:00:00'::interval))
         ->  Sort  (cost=252776.54..255312.51 rows=1014389 width=8) (actual time=32001.370..33473.407 rows=1051395
loops=1)
               Sort Key: date_part('doy'::text, readings."when")
               ->  Seq Scan on readings  (cost=0.00..142650.89 rows=1014389 width=8) (actual time=0.053..13759.015
rows=1014448loops=1) 
 Total runtime: 61720.935 ms
(12 rows)

weather=# \d minute.windspeed
          Table "minute.windspeed"
   Column    |       Type       | Modifiers
-------------+------------------+-----------
 time_group  | integer          | not null
 min_reading | double precision | not null
 max_reading | double precision | not null
 avg_reading | double precision | not null
Indexes:
    "windspeed_pkey" PRIMARY KEY, btree (time_group)
    "minute_windspeed_index" btree (unmunge_time(time_group))

CREATE OR REPLACE FUNCTION unmunge_time( integer )
RETURNS timestamp AS '
DECLARE
   input ALIAS FOR $1;
BEGIN
   RETURN (''epoch''::timestamptz + input * ''1sec''::interval)::timestamp;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

weather=# \d readings
                                             Table "public.readings"
        Column        |            Type             |                          Modifiers
----------------------+-----------------------------+-------------------------------------------------------------
 when                 | timestamp without time zone | not null default (timeofday())::timestamp without time zone
 hour_group           | integer                     |
 minute_group         | integer                     |
 day_group            | integer                     |
 week_group           | integer                     |
 month_group          | integer                     |
 year_group           | integer                     |
 year_group_updated   | boolean                     | default false
 month_group_updated  | boolean                     | default false
 week_group_updated   | boolean                     | default false
 day_group_updated    | boolean                     | default false
 hour_group_updated   | boolean                     | default false
 minute_group_updated | boolean                     | default false
Indexes:
    "readings_pkey" PRIMARY KEY, btree ("when")
    "day_group_updated_index" btree (day_group_updated, day_group)
    "hour_group_updated_index" btree (hour_group_updated, hour_group)
    "month_group_updated_index" btree (month_group_updated, month_group)
    "readings_doy_index" btree (date_part('doy'::text, "when"))
    "week_group_updated_index" btree (week_group_updated, week_group)
    "year_group_updated_index" btree (year_group_updated, year_group)
Triggers:
    munge_time BEFORE INSERT OR UPDATE ON readings FOR EACH ROW EXECUTE PROCEDURE munge_time()

readings_doy is a view that adds date_part('doy'::text, readings."when") AS doy to the readings table.

Thanks,
Rob

--
 21:15:51 up 2 days, 13:42,  9 users,  load average: 3.14, 2.63, 2.62
Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006