version 7.4 results:


explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN music.program P ON
music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
WHERE L.station = UPPER('kabc')::VARCHAR
AND L.air_date = '04/12/2002'::TIMESTAMP
AND P.cutoff_date IS NULL
ORDER BY L.chron_start,L.chron_end;


QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=17595.99..17608.23 rows=4894 width=12) (actual time=8083.719..8083.738 rows=30 loops=1)
Sort Key: l.chron_start, l.chron_end
-> Merge Left Join (cost=17135.92..17296.07 rows=4894 width=12) (actual time=7727.590..8083.349 rows=30 loops=1)
Merge Cond: ("outer"."?column5?" = "inner"."?column3?")
Filter: ("inner".cutoff_date IS NULL)
-> Sort (cost=1681.69..1682.73 rows=414 width=21) (actual time=1.414..1.437 rows=30 loops=1)
Sort Key: (l.program_id)::text
-> Index Scan using idx_logfile_station_air_date on logfile l (cost=0.00..1663.70 rows=414 width=21) (actual time=0.509..1.228 rows=30 loops=1)
Index Cond: (((station)::text = 'KABC'::text) AND (air_date = '2002-04-12 00:00:00'::timestamp without time zone))
-> Sort (cost=15454.22..15465.06 rows=4335 width=20) (actual time=7718.612..7869.874 rows=152779 loops=1)
Sort Key: (music.fn_mri_id_no_program(p.mri_id_no))::text
-> Seq Scan on program p (cost=0.00..15192.35 rows=4335 width=20) (actual time=109.045..1955.882 rows=173998 loops=1)
Total runtime: 8194.290 ms
(13 rows)



version 7.3 results:


explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN music.program P ON
music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
WHERE L.station = UPPER('kabc')::VARCHAR
AND L.air_date = '04/12/2002'::TIMESTAMP
AND P.cutoff_date IS NULL
ORDER BY L.chron_start,L.chron_end;


QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=55765.51..55768.33 rows=1127 width=41) (actual time=7.74..7.75 rows=30 loops=1)
Sort Key: l.chron_start, l.chron_end
-> Nested Loop (cost=0.00..55708.36 rows=1127 width=41) (actual time=0.21..7.62 rows=30 loops=1)
Filter: ("inner".cutoff_date IS NULL)
-> Index Scan using idx_logfile_station_air_date on logfile l (cost=0.00..71.34 rows=17 width=21) (actual time=0.14..0.74 rows=30 loops=1)
Index Cond: ((station = 'KABC'::character varying) AND (air_date = '2002-04-12 00:00:00'::timestamp without time zone))
-> Index Scan using idx_program_mri_id_no_program on program p (cost=0.00..3209.16 rows=870 width=20) (actual time=0.05..0.22 rows=9 loops=30)
Index Cond: (music.fn_mri_id_no_program(p.mri_id_no) = "outer".program_id)
Total runtime: 7.86 msec



---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to