Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2 - Mailing list pgsql-performance

Στις 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.11rows=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.30rows=1 width=4) 
                                                           Index Cond: (vslold.id = msold.vslid)
                                               ->  Index Scan using mariner_pkey on mariner mold  (cost=0.00..0.92
rows=1width=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=14286width=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=4590width=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.66rows=41307 width=24) 
                                                                       Filter: ((state)::text = 'Active'::text)
                                                     ->  Index Scan using vessels_pkey on vessels vslold
(cost=0.00..0.28rows=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