pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Deucher <alexdeucher@gmail.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: postgres 8.2 seems to prefer Seq Scan
Date: Fri, 6 Apr 2007 16:38:33 -0400
Message-ID: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com> (raw)

One more anomaly between 7.4 and 8.2.  DB dumped from 7.4 and loaded
onto 8.2, both have locale set to C.  8.2 seems to prefer Seq Scans
for the first query while the ordering in the second query seems to
perform worse on 8.2.  I ran analyze.  I've tried with the encoding
set to UTF-8 and SQL_ASCII; same numbers and plans.  Any ideas how to
improve this?

Thanks,

Alex

postgres 7.4

EXPLAIN ANALYZE select pnum, event_pid, code_name, code_description,
code_mcam, event_date, effective_date, ref_country,
ref_country_legal_code, corresponding_pnum, withdrawal_date,
payment_date, extension_date, fee_payment_year, requester, free_form
from code inner join event on code_pid = code_pid_fk where pnum
='AB5819188';
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..60.87 rows=19 width=231) (actual
time=0.065..0.065 rows=0 loops=1)
   ->  Index Scan using pnum_idx on event  (cost=0.00..3.37 rows=19
width=172) (actual time=0.063..0.063 rows=0 loops=1)
         Index Cond: ((pnum)::text = 'AB5819188'::text)
   ->  Index Scan using code_pkey on code  (cost=0.00..3.01 rows=1
width=67) (never executed)
         Index Cond: (code.code_pid = "outer".code_pid_fk)
 Total runtime: 0.242 ms
(6 rows)


postgres 8.2

EXPLAIN ANALYZE select pnum, event_pid, code_name, code_description,
code_mcam, event_date, effective_date, ref_country,
ref_country_legal_code, corresponding_pnum, withdrawal_date,
payment_date, extension_date, fee_payment_year, requester, free_form
from code inner join event on code_pid = code_pid_fk where pnum
='AB5819188';
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=106.91..3283.46 rows=1779 width=230) (actual
time=10.383..10.390 rows=1 loops=1)
   Hash Cond: (event.code_pid_fk = code.code_pid)
   ->  Index Scan using pnum_idx on event  (cost=0.00..3147.63
rows=1779 width=171) (actual time=0.030..0.033 rows=1 loops=1)
         Index Cond: ((pnum)::text = 'AB5819188'::text)
   ->  Hash  (cost=70.85..70.85 rows=2885 width=67) (actual
time=10.329..10.329 rows=2885 loops=1)
         ->  Seq Scan on code  (cost=0.00..70.85 rows=2885 width=67)
(actual time=0.013..4.805 rows=2885 loops=1)
 Total runtime: 10.490 ms
(7 rows)


postgres 7.4

EXPLAIN ANALYZE select e.pnum, c.code_description, c.code_mcam,
e.event_pid from event e, code c where c.code_name =
e.ref_country_legal_code and c.code_country = e.ref_country and e.pnum
= 'AB5819188';

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3.47..106.28 rows=1 width=73) (actual
time=7.795..7.795 rows=0 loops=1)
   Hash Cond: ((("outer".code_name)::text =
("inner".ref_country_legal_code)::text) AND
(("outer".code_country)::text = ("inner".ref_country)::text))
   ->  Seq Scan on code c  (cost=0.00..63.92 rows=2592 width=69)
(actual time=0.010..3.881 rows=2592 loops=1)
   ->  Hash  (cost=3.37..3.37 rows=19 width=30) (actual
time=0.064..0.064 rows=0 loops=1)
         ->  Index Scan using pnum_idx on event e  (cost=0.00..3.37
rows=19 width=30) (actual time=0.062..0.062 rows=0 loops=1)
               Index Cond: ((pnum)::text = 'AB5819188'::text)
 Total runtime: 7.947 ms
(7 rows)


postgres 8.2

EXPLAIN ANALYZE select e.pnum, c.code_description, c.code_mcam,
e.event_pid from event e, code c where c.code_name =
e.ref_country_legal_code and c.code_country = e.ref_country and e.pnum
= 'AB5819188';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=114.12..3368.51 rows=1 width=73) (actual
time=10.900..10.900 rows=0 loops=1)
   Hash Cond: (((e.ref_country_legal_code)::text =
(c.code_name)::text) AND ((e.ref_country)::text =
(c.code_country)::text))
   ->  Index Scan using pnum_idx on event e  (cost=0.00..3147.63
rows=1779 width=30) (actual time=0.027..0.031 rows=1 loops=1)
         Index Cond: ((pnum)::text = 'AB5819188'::text)
   ->  Hash  (cost=70.85..70.85 rows=2885 width=69) (actual
time=10.838..10.838 rows=2885 loops=1)
         ->  Seq Scan on code c  (cost=0.00..70.85 rows=2885 width=69)
(actual time=0.011..4.863 rows=2885 loops=1)
 Total runtime: 11.018 ms
(7 rows)



view thread (6+ messages)  latest in thread

Message-ID: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com>
Permalink:  ../a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com/
Also on:    postgresql.org/message-id/a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alexdeucher@gmail.com
  Subject: Re: postgres 8.2 seems to prefer Seq Scan
  In-Reply-To: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com>
  References: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.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