Στις Tuesday 18 January 2011 16:26:21 ο/η Mladen Gogala έγραψε:
> This leads me to the conclusion that the queries differ significantly.
> 8.3.3 mentions NOT hashed plan, I don't see it in 9.02 and the filtering
> conditions look differently. Are you sure that the plans are from the
> same query?
First the num of rows in the two portions are different so you might be
comparing apples and oranges here.
Anyway, i will repost the EXPLAIN plans by copying pasting the query, without
the analyze part.
8.3.13
Unique (cost=633677.56..633700.48 rows=1834 width=23)
-> Sort (cost=633677.56..633682.14 rows=1834 width=23)
Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)),
(COALESCE(m.midname, ''::character varying)), m.id
-> Hash Join (cost=630601.65..633578.15 rows=1834 width=23)
Hash Cond: (ms.vslid = vsl.id)
-> Hash Join (cost=630580.33..633530.01 rows=2261 width=27)
Hash Cond: (ms.marinerid = m.id)
-> Seq Scan on marinerstates ms (cost=0.00..2875.32
rows=4599 width=8)
Filter: (((state)::text = 'Active'::text) AND
((starttime)::date <= '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date
>= '2006-07-15'::date))
-> Hash (cost=630491.54..630491.54 rows=7103 width=23)
-> Index Scan using mariner_pkey on mariner m
(cost=628776.89..630491.54 rows=7103 width=23)
Filter: ((NOT (hashed subplan)) AND
((marinertype)::text = 'Mariner'::text))
SubPlan
-> Unique (cost=0.00..628772.30 rows=1834
width=4)
-> Nested Loop (cost=0.00..628767.72
rows=1834 width=4)
-> Nested Loop
(cost=0.00..627027.98 rows=1865 width=4)
-> Index Scan using
marinerstates_marinerid on marinerstates msold (cost=0.00..626316.07 rows=2299
width=8)
Filter:
(((state)::text = 'Active'::text) AND ((starttime)::date <= '2007-01-11'::date)
AND ((COALESCE(endtime, now()))::date >= '2006-07-15'::date) AND (subplan))
SubPlan
-> Bitmap Heap
Scan on marinerstates msold2 (cost=4.28..12.11 rows=1 width=0)
Recheck
Cond: ((marinerid = $0) AND (starttime < $2))
Filter: ((id
<> $1) AND ((state)::text = 'Active'::text) AND (($2 - endtime) <= '1 year 6
mons'::interval))
-> Bitmap
Index Scan on marinerstates_marinerid_starttime (cost=0.00..4.28 rows=2
width=0)
Index
Cond: ((marinerid = $0) AND (starttime < $2))
-> Index Scan using
vessels_pkey on vessels vslold (cost=0.00..0.30 rows=1 width=4)
Index Cond:
(vslold.id = msold.vslid)
-> Index Scan using
mariner_pkey on mariner mold (cost=0.00..0.92 rows=1 width=4)
Index Cond: (mold.id =
msold.marinerid)
Filter:
((mold.marinertype)::text = 'Mariner'::text)
-> Hash (cost=17.81..17.81 rows=281 width=4)
-> Seq Scan on vessels vsl (cost=0.00..17.81 rows=281
width=4)
(31 rows)
9.0.2
Unique (cost=11525.09..11571.55 rows=3717 width=23)
-> Sort (cost=11525.09..11534.38 rows=3717 width=23)
Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)),
(COALESCE(m.midname, ''::character varying)), m.id
-> Hash Join (cost=8281.98..11304.67 rows=3717 width=23)
Hash Cond: (ms.marinerid = m.id)
-> Hash Join (cost=20.12..2963.83 rows=3717 width=4)
Hash Cond: (ms.vslid = vsl.id)
-> Seq Scan on marinerstates ms (cost=0.00..2889.32
rows=4590 width=8)
Filter: (((state)::text = 'Active'::text) AND
((starttime)::date <= '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date
>= '2006-07-15'::date))
-> Hash (cost=16.72..16.72 rows=272 width=4)
-> Seq Scan on vessels vsl (cost=0.00..16.72
rows=272 width=4)
-> Hash (cost=8172.57..8172.57 rows=7143 width=23)
-> Seq Scan on mariner m (cost=7614.86..8172.57
rows=7143 width=23)
Filter: ((NOT (hashed SubPlan 1)) AND
((marinertype)::text = 'Mariner'::text))
SubPlan 1
-> Unique (cost=2768.00..7614.86 rows=1 width=4)
-> Nested Loop (cost=2768.00..7614.86
rows=1 width=4)
Join Filter: (msold.marinerid =
mold.id)
-> Index Scan using mariner_pkey on
mariner mold (cost=0.00..1728.60 rows=14286 width=4)
Filter: ((marinertype)::text =
'Mariner'::text)
-> Materialize
(cost=2768.00..5671.97 rows=1 width=8)
-> Nested Loop
(cost=2768.00..5671.96 rows=1 width=8)
-> Hash Semi Join
(cost=2768.00..5671.67 rows=1 width=12)
Hash Cond:
(msold.marinerid = msold2.marinerid)
Join Filter:
((msold2.id <> msold.id) AND (msold2.starttime < msold.starttime) AND
((msold.starttime - msold2.endtime) <= '1 year 6 mons'::interval))
-> Seq Scan on
marinerstates msold (cost=0.00..2889.32 rows=4590 width=20)
Filter:
(((state)::text = 'Active'::text) AND ((starttime)::date <= '2007-01-11'::date)
AND ((COALESCE(endtime, now()))::date >= '2006-07-15'::date))
-> Hash
(cost=2251.66..2251.66 rows=41307 width=24)
-> Seq Scan
on marinerstates msold2 (cost=0.00..2251.66 rows=41307 width=24)
Filter:
((state)::text = 'Active'::text)
-> Index Scan using
vessels_pkey on vessels vslold (cost=0.00..0.28 rows=1 width=4)
Index Cond:
(vslold.id = msold.vslid)
(32 rows)
>
> --
> Mladen Gogala
> Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> www.vmsinfo.com
>
>
--
Achilleas Mantzios
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
