I just installed v7.4 and restored a database from v7.3.4. I have an
index based on a function that the planner is using on the old version,
but doing seq scans on left joins in the new version. I have run
analyze on the table post restore. the query returns in less than 1
second on version 7.3.4 and takes over 10 seconds on version 7.4. Any
help will be appreciated.
Roger Ging
Query:
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;
planner results on 7.4:
Sort (cost=17595.99..17608.23 rows=4894 width=12) Sort Key: l.chron_start, l.chron_end -> Merge Left Join
(cost=17135.92..17296.07rows=4894 width=12) Merge Cond: ("outer"."?column5?" = "inner"."?column3?")
Filter:("inner".cutoff_date IS NULL) -> Sort (cost=1681.69..1682.73 rows=414 width=21) 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) 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) 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)
planner results on 7.3.4:
Sort (cost=55765.51..55768.33 rows=1127 width=41) Sort Key: l.chron_start, l.chron_end -> Nested Loop
(cost=0.00..55708.36rows=1127 width=41) Filter: ("inner".cutoff_date IS NULL) -> Index Scan using
idx_logfile_station_air_dateon logfile l (cost=0.00..71.34 rows=17 width=21) Index Cond: ((station =
'KABC'::charactervarying) AND
(air_date = '2002-04-12 00:00:00'::timestamp without time zone)) -> Index Scan using
idx_program_mri_id_no_programon program
p (cost=0.00..3209.16 rows=870 width=20) Index Cond: (music.fn_mri_id_no_program(p.mri_id_no) =
"outer".program_id)
table "Program" details:
Column | Type | Modifiers
----------------+-----------------------------+----------- record_id | integer | title
| character varying(40) | mri_id_no | character varying(8) | ascap_cat | character varying(1)
| ascap_mult | numeric(5,3) | ascap_prod | character varying(10) | npa_ind |
charactervarying(3) | non_inc_in | character varying(1) | as_pr_su | character varying(1)
| as_1st_run | character varying(1) | as_cue_st | character varying(1) | bmi_cat |
charactervarying(2) | bmi_mult | numeric(6,2) | bmi_prod | character varying(7)
| year | integer | prog_type | character varying(1) | total_ep |
integer | last_epis | character varying(3) | syndicator | character varying(6)
| station | character varying(4) | syn_loc | character varying(1) | spdb_ver |
charactervarying(4) | as_filed | character varying(4) | bmidb_ver | character varying(4)
| cutoff_date | timestamp without time zone | effective_date | timestamp without time zone | program_id |
charactervarying(5) |
Indexes: "idx_program_mri_id_no" btree (mri_id_no) "idx_program_mri_id_no_program" btree
(music.fn_mri_id_no_program(mri_id_no)) "idx_program_program_id" btree (program_id) "program_mri_id_no" btree
(mri_id_no) "program_oid" btree (oid)