X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 360F3D1B43F for ; Wed, 26 Nov 2003 18:39:56 +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 86685-10 for ; Wed, 26 Nov 2003 14:39:31 -0400 (AST) Received: from pcs-1.paccomsys.com (208.225.nwc.net [207.151.225.208]) by svr1.postgresql.org (Postfix) with ESMTP id C87F5D1B431 for ; Wed, 26 Nov 2003 14:39:23 -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 hAQIFm74012678 for ; Wed, 26 Nov 2003 10:15:49 -0800 Message-ID: <3FC4F360.2090609@paccomsys.com> Date: Wed, 26 Nov 2003 10:39:28 -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-performance@postgresql.org Subject: Followup - expression (functional) index use in joins 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/272 X-Sequence-Number: 4832 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