X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A03DDD1C943 for ; Mon, 24 Nov 2003 21:02:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 33649-09 for ; Mon, 24 Nov 2003 17:01:46 -0400 (AST) Received: from pcs-1.paccomsys.com (208.225.nwc.net [207.151.225.208]) by svr1.postgresql.org (Postfix) with ESMTP id 2DC96D1D885 for ; Mon, 24 Nov 2003 17:01:34 -0400 (AST) Received: from paccomsys.com (mail.musicreports.com [64.161.179.34]) by pcs-1.paccomsys.com (8.12.5/8.12.5) with ESMTP id hAOKaf74022773 for ; Mon, 24 Nov 2003 12:36:42 -0800 Message-ID: <3FC271A9.6070008@paccomsys.com> Date: Mon, 24 Nov 2003 13:01:29 -0800 From: Roger Ging User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030708 Debian/1.3-4.lindows43 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: functional index and the planner (v7.4) Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/329 X-Sequence-Number: 16144 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)