pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (7+ messages)  latest in thread

Message-ID: <3FC4F360.2090609@paccomsys.com>
Permalink:  ../3FC4F360.2090609@paccomsys.com/
Also on:    postgresql.org/message-id/3FC4F360.2090609@paccomsys.com

 · 

reply

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