From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | 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 |
Date: | 2011-01-21 17:09:12 |
Message-ID: | AANLkTimeC41oJ34yzPPf3wc_qQ_mKT_=krCcD7OMEYsc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Jan 20, 2011 at 2:05 AM, Achilleas Mantzios
<achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> -> Hash Semi Join (cost=2768.00..5671.67 rows=1 width=12) (actual time=39.249..81.025 rows=1876 loops=1)
> 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) (actual time=0.003..33.964 rows=2625 loops=1)
> 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) (actual time=39.156..39.156 rows=41250 loops=1)
> Buckets: 8192 Batches: 1 Memory Usage: 2246kB
> -> Seq Scan on marinerstates msold2 (cost=0.00..2251.66 rows=41307 width=24) (actual time=0.002..24.552 rows=41250 loops=1)
> Filter: ((state)::text = 'Active'::text)
Looks like the bad selectivity estimate there is what's killing it.
Not sure I completely understand why 9.0.2 is coming up with such a
bad estimate, though.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2011-01-21 17:13:57 | Re: Best way to get the latest revision from a table |
Previous Message | Tom Lane | 2011-01-20 21:11:33 | Re: copy command and blobs |