pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
expression (functional) index use in joins
2+ messages / 2 participants
[nested] [flat]

* expression (functional) index use in joins
@ 2003-11-26 16:38 Roger Ging <rging@paccomsys.com>
  2003-11-26 17:52 ` Re: expression (functional) index use in joins Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Roger Ging @ 2003-11-26 16:38 UTC (permalink / raw)
  To: pgsql-performance

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)




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: expression (functional) index use in joins
  2003-11-26 16:38 expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
@ 2003-11-26 17:52 ` Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2003-11-26 17:52 UTC (permalink / raw)
  To: Roger Ging <rging@paccomsys.com>; pgsql-performance

On Wednesday 26 November 2003 16:38, Roger Ging wrote:
> I just installed v7.4 and restored a database from v7.3.4.
[snip]

Hmm - you seem to be getting different row estimates in the plan. Can you 
re-analyse both versions and post EXPLAIN ANALYSE rather than just EXPLAIN?

>                 ->  Seq Scan on program p  (cost=0.00..15192.35
> rows=4335 width=20)
>
> planner results on 7.3.4:
>
>           ->  Index Scan using idx_program_mri_id_no_program on program
> p  (cost=0.00..3209.16 rows=870 width=20)

-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-11-26 17:52 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-26 16:38 expression (functional) index use in joins Roger Ging <rging@paccomsys.com>
2003-11-26 17:52 ` Richard Huxton <dev@archonet.com>

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