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)
