From: Roger Ging <rging@paccomsys.com>
To: pgsql-performance@postgresql.org
Subject: Followup - expression (functional) index use in joins
Date: Wed, 26 Nov 2003 10:39:28 -0800
Message-ID: <3FC4F360.2090609@paccomsys.com> (raw)
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
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-performance@postgresql.org
Cc: rging@paccomsys.com
Subject: Re: Followup - expression (functional) index use in joins
In-Reply-To: <3FC4F360.2090609@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