Index isn't used during a join.

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.675 rows=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=284 loops=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=1014448 loops=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