From: Roger Ging <rging@paccomsys.com>
To: pgsql-sql@postgresql.org
Subject: functional index and the planner (v7.4)
Date: Mon, 24 Nov 2003 13:01:29 -0800
Message-ID: <3FC271A9.6070008@paccomsys.com> (raw)
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.07 rows=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.36 rows=1127 width=41)
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)
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)
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 | character varying(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 | character varying(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 | character varying(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 | character varying(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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rging@paccomsys.com
Subject: Re: functional index and the planner (v7.4)
In-Reply-To: <3FC271A9.6070008@paccomsys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox