Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 377076338B3 for ; Tue, 20 Apr 2010 16:34:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 82005-01-4 for ; Tue, 20 Apr 2010 19:33:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f214.google.com (mail-gx0-f214.google.com [209.85.217.214]) by mail.postgresql.org (Postfix) with ESMTP id 80B856334E1 for ; Tue, 20 Apr 2010 16:33:33 -0300 (ADT) Received: by gxk6 with SMTP id 6so240209gxk.14 for ; Tue, 20 Apr 2010 12:33:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=aCgB2yeN0VLFZv2upIcQbUazV8n9eJikZCTB+hNWxVQ=; b=PhrUd0XpbNtUJUoPjUresXV01dN+feiYmDj/Rakjry7T3QN8nnTZosg/6lqmdQhfB6 Vj89ZcKsoc7RRYl4myG0IKtYYQWnNDUvGaarhGij2aAjK+McuJL+8HPKJa/9a86E3xu6 BZpluqvv6aLRB1kQPfy5DVQCp+EbmfRZTUx1Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=B03Xea8dQWpjuXqkEIPYBT2S+vMkRjVGYTwc7o7f3tzVIrsLt1BnR+YGwszOOZmrT5 ZPe8uyP8HnobWMPUJxSIl7PeUv3295D5Sx9YPRwMOj7cm8XlTBQSgNTVCcGuUhgxpl/j RdT1alJ+YUs+UF2OeZZ+vxo1mNWpIokztxWB8= MIME-Version: 1.0 Received: by 10.150.218.9 with HTTP; Tue, 20 Apr 2010 12:33:32 -0700 (PDT) In-Reply-To: <4BCDF48C.6000409@musicreports.com> References: <4BCDF48C.6000409@musicreports.com> Date: Tue, 20 Apr 2010 13:33:32 -0600 Received: by 10.150.128.36 with SMTP id a36mr7629498ybd.337.1271792012604; Tue, 20 Apr 2010 12:33:32 -0700 (PDT) Message-ID: Subject: Re: performance change from 8.3.1 to later releases From: Scott Marlowe To: Roger Ging Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.079 tagged_above=-10 required=5 tests=AWL=-0.179, BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201004/262 X-Sequence-Number: 38512 On Tue, Apr 20, 2010 at 12:38 PM, Roger Ging wrote= : > Hi, > > I have access to servers running 8.3.1, 8.3.8, 8.4.2 and 8.4.3. =A0I have > noticed that on the 8.4.* versions, a lot of our code is either taking mu= ch > longer to complete, or never completing. =A0I think I have isolated the > problem to queries using in(), not in() or not exists(). =A0I've put toge= ther > a test case with one particular query that demonstrates the problem. > > select count(*) from traderhank.vendor_catalog =3D 147,352 > > select count(*) from traderhank.xc_products =3D 8,610 > > The sub query (select vc1.th_sku from traderhank.vendor_catalog vc1 > join traderhank.vendor_catalog vc2 on vc2.short_desc_75 =3D vc1.short_des= c_75 > and vc2.th_sku !=3D vc1.th_sku > where vc1.cutoff_date is null and vc2.cutoff_date is null > group by vc1.th_sku > ) =A0yields 54,390 rows > > The sub query (select vc_th_Sku from traderhank.xc_products where vc_th_s= ku > is not null) yields 5,132 rows > > These 2 tables have been loaded from a pg_dump on all servers, vacuum > analyze run after load. > > 1st case: pg 8.3.1 using left join finishes the query in about 3.5 second= s > > explain analyze > select vc.* from traderhank.vendor_catalog vc > left join > ( > select vc1.th_sku from traderhank.vendor_catalog vc1 > join traderhank.vendor_catalog vc2 on vc2.short_desc_75 =3D vc1.short_des= c_75 > and vc2.th_sku !=3D vc1.th_sku > where vc1.cutoff_date is null and vc2.cutoff_date is null > group by vc1.th_sku > ) vcj on vcj.th_sku =3D vc.th_sku > left join traderhank.xc_products xc on xc.vc_th_sku =3D vc.th_sku > where vcj.th_sku is null > and xc.vc_th_sku is null > > "Merge Left Join =A0(cost=3D71001.53..72899.35 rows=3D36838 width=3D310) = (actual > time=3D9190.446..10703.509 rows=3D78426 loops=3D1)" > " =A0Merge Cond: ((vc.th_sku)::text =3D (vc1.th_sku)::text)" > " =A0Filter: (vc1.th_sku IS NULL)" > " =A0-> =A0Merge Left Join =A0(cost=3D19362.72..20201.46 rows=3D73676 wid= th=3D310) > (actual time=3D917.947..1784.593 rows=3D141962 loops=3D1)" > " =A0 =A0 =A0 =A0Merge Cond: ((vc.th_sku)::text =3D (xc.vc_th_sku)::text)= " > " =A0 =A0 =A0 =A0Filter: (xc.vc_th_sku IS NULL)" > " =A0 =A0 =A0 =A0-> =A0Sort =A0(cost=3D17630.88..17999.26 rows=3D147352 w= idth=3D310) (actual > time=3D871.130..1114.453 rows=3D147352 loops=3D1)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: vc.th_sku" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Method: =A0quicksort =A0Memory: 45285kB= " > " =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on vendor_catalog vc =A0(cost= =3D0.00..4981.52 > rows=3D147352 width=3D310) (actual time=3D0.020..254.023 rows=3D147352 lo= ops=3D1)" > " =A0 =A0 =A0 =A0-> =A0Sort =A0(cost=3D1731.84..1753.37 rows=3D8610 width= =3D8) (actual > time=3D46.783..62.347 rows=3D9689 loops=3D1)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: xc.vc_th_sku" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Method: =A0quicksort =A0Memory: 734kB" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on xc_products xc =A0(cost=3D= 0.00..1169.10 rows=3D8610 > width=3D8) (actual time=3D0.013..25.490 rows=3D8610 loops=3D1)" > " =A0-> =A0Sort =A0(cost=3D51638.80..51814.57 rows=3D70309 width=3D32) (a= ctual > time=3D8272.483..8382.258 rows=3D66097 loops=3D1)" > " =A0 =A0 =A0 =A0Sort Key: vc1.th_sku" > " =A0 =A0 =A0 =A0Sort Method: =A0quicksort =A0Memory: 4086kB" So here we get a hash agg in ~4M memory: > " =A0 =A0 =A0 =A0-> =A0HashAggregate =A0(cost=3D44572.25..45275.34 rows= =3D70309 width=3D8) > (actual time=3D7978.928..8080.317 rows=3D54390 loops=3D1)" And the row estimate is similar. (much deleted) > on any version from 8.3.8 on, this query has never returned, and explain > analyze never returns, so I am only posting explain output We get a Seq Scan with a huge cost, and no hash agg or quick sort. Is the work_mem the same or similar? I'd crank it up for testing just to see if it helps. 16Meg is pretty safe on a low traffic machine. > "Seq Scan on vendor_catalog vc =A0(cost=3D140413.05..91527264.28 rows=3D3= 6838 > width=3D309)" > " =A0Filter: ((NOT (hashed SubPlan 2)) AND (NOT (SubPlan 1)))" > " =A0SubPlan 2" > " =A0 =A0-> =A0Seq Scan on xc_products =A0(cost=3D0.00..1716.99 rows=3D51= 32 width=3D8)" > " =A0 =A0 =A0 =A0 =A0Filter: (vc_th_sku IS NOT NULL)" > " =A0SubPlan 1" > " =A0 =A0-> =A0Materialize =A0(cost=3D138683.23..139734.64 rows=3D75541 w= idth=3D8)" > " =A0 =A0 =A0 =A0 =A0-> =A0Group =A0(cost=3D134997.43..138311.69 rows=3D7= 5541 width=3D8)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Sort =A0(cost=3D134997.43..136654.= 56 rows=3D662853 width=3D8)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: vc1.th_sku" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Merge Join =A0(cost=3D= 39600.73..52775.08 rows=3D662853 > width=3D8)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Merge Cond: ((vc= 1.short_desc_75)::text =3D > (vc2.short_desc_75)::text)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Join Filter: ((v= c2.th_sku)::text <> > (vc1.th_sku)::text)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Sort =A0(c= ost=3D19800.37..20062.75 rows=3D104954 > width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort= Key: vc1.short_desc_75" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> = =A0Seq Scan on vendor_catalog vc1 > =A0(cost=3D0.00..8534.52 rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Filter: ((cutoff_date IS NULL) AND > (th_sku IS NOT NULL))" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Materializ= e =A0(cost=3D19800.37..21112.29 > rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> = =A0Sort =A0(cost=3D19800.37..20062.75 > rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Sort Key: vc2.short_desc_75" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0-> =A0Seq Scan on vendor_catalog vc2 > =A0(cost=3D0.00..8534.52 rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0Filter: (cutoff_date IS > NULL)" > > > > > I've also tried changing the code to not exists, but that query never com= es > back on any version I have available: > > explain --analyze > select vc.* from traderhank.vendor_catalog vc > where not exists > ( > select 1 from traderhank.vendor_catalog vc1 > join traderhank.vendor_catalog vc2 on vc2.short_desc_75 =3D vc1.short_des= c_75 > and vc2.th_sku !=3D vc1.th_sku > where vc1.cutoff_date is null and vc2.cutoff_date is null and vc1.th_sku = =3D > vc.th_sku > group by vc1.th_sku > ) > and not exists > (select 1 from traderhank.xc_products where vc_th_sku is not null and > vc_th_sku =3D vc.th_sku) > > "Nested Loop Anti Join =A0(cost=3D63650.74..93617.53 rows=3D1 width=3D309= )" > " =A0Join Filter: ((xc_products.vc_th_sku)::text =3D (vc.th_sku)::text)" > " =A0-> =A0Hash Anti Join =A0(cost=3D63650.74..91836.39 rows=3D1 width=3D= 309)" > " =A0 =A0 =A0 =A0Hash Cond: ((vc.th_sku)::text =3D (vc1.th_sku)::text)" > " =A0 =A0 =A0 =A0-> =A0Seq Scan on vendor_catalog vc =A0(cost=3D0.00..853= 4.52 rows=3D147352 > width=3D309)" > " =A0 =A0 =A0 =A0-> =A0Hash =A0(cost=3D52775.08..52775.08 rows=3D662853 w= idth=3D8)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Merge Join =A0(cost=3D39600.73..52775.= 08 rows=3D662853 > width=3D8)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Merge Cond: ((vc1.short_desc_75)= ::text =3D > (vc2.short_desc_75)::text)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Join Filter: ((vc2.th_sku)::text= <> > (vc1.th_sku)::text)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Sort =A0(cost=3D19800.37..= 20062.75 rows=3D104954 > width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: vc1.short_= desc_75" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on ve= ndor_catalog vc1 > =A0(cost=3D0.00..8534.52 rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: = (cutoff_date IS NULL)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Materialize =A0(cost=3D198= 00.37..21112.29 rows=3D104954 > width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Sort =A0(cost= =3D19800.37..20062.75 rows=3D104954 > width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key= : vc2.short_desc_75" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Se= q Scan on vendor_catalog vc2 > =A0(cost=3D0.00..8534.52 rows=3D104954 width=3D27)" > " =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0Filter: (cutoff_date IS NULL)" > " =A0-> =A0Seq Scan on xc_products =A0(cost=3D0.00..1716.99 rows=3D5132 w= idth=3D8)" > " =A0 =A0 =A0 =A0Filter: (xc_products.vc_th_sku IS NOT NULL)" > > > > So, my question is, do I need =A0to re-write all of my in() and not in () > queries to left joins, is this something that might get resolved in anoth= er > release in the future? > > Thanks for any help. > > Roger Ging > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --=20 When fascism comes to America, it will be intolerance sold as diversity.