Followup - expression (functional) index use in joins - Mailing list pgsql-performance

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