X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9E461DB7A9
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Nov 2005 20:10:12 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 41912-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 15 Nov 2005 00:10:09 +0000 (GMT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 3AA00DB7A6
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Nov 2005 20:10:11 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id 36so1382023wra
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Nov 2005 16:10:14 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=ikg1ACjRybQ6Ts8HvcLonNbaKm+Hb3S/37Ou9evBl36fHuFsgDd/ESRcLaDC6jGqNt8QXPjuBMGNtZ4kltI6+FkRpxUv2yZqh+s2xm39CzrS9Dyv+Nr0aTjdpDfjKVPiKSBkNN2abGEBNumL+qCaKpbtGjbNOkqSRM6BECs97EA=
Received: by 10.65.153.12 with SMTP id f12mr6428102qbo;
	Mon, 14 Nov 2005 16:10:14 -0800 (PST)
Received: by 10.64.243.11 with HTTP; Mon, 14 Nov 2005 16:10:14 -0800 (PST)
Message-ID: <38242de90511141610j621d9a02r52a1d6f4fc55cb55@mail.gmail.com>
Date: Mon, 14 Nov 2005 17:10:14 -0700
From: Joshua Marsh <icub3d@gmail.com>
To: Postgres-performance <pgsql-performance@postgresql.org>
Subject: Re: Help speeding up delete
In-Reply-To: <43792355.1020501@noao.edu>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_7809_18807905.1132013414563"
References: <43790A99.9050603@noao.edu>
	<5E90D296-FA4A-4E48-92B4-396AADE35EF2@slamb.org>
	<43792355.1020501@noao.edu>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200511/235
X-Sequence-Number: 15492

------=_Part_7809_18807905.1132013414563
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 11/14/05, Steve Wampler <swampler@noao.edu> wrote:

> However, even that seems to have a much higher cost than I'd expect:
>
> lab.devel.configdb=3D# explain delete from "tmp_table2" where id in
> (select id from tmp_table2 where name=3D'obsid' and value =3D 'oid080505'=
);
> NOTICE: QUERY PLAN:
>
> Seq Scan on tmp_table2 (cost=3D0.00..65705177237.26 rows=3D769844 width=
=3D6)
> SubPlan
> -> Materialize (cost=3D42674.32..42674.32 rows=3D38 width=3D50)
> -> Seq Scan on tmp_table2 (cost=3D0.00..42674.32 rows=3D38 width=3D50)
>

For one reason or the other, the planner things a sequential scan is the
best solution. Try turning off seq_scan before the query and see if it
changes the plan (set enable_seqscan off;).

I've seen this problem with sub queries and that usually solves it.

--
This E-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521 and is legally privileged.

This information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this message
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited.

------=_Part_7809_18807905.1132013414563
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<br><br><div><span class=3D"gmail_quote">On 11/14/05, <b class=3D"gmail_sen=
dername">Steve Wampler</b> &lt;<a href=3D"mailto:swampler@noao.edu">swample=
r@noao.edu</a>&gt; wrote:</span><br><blockquote class=3D"gmail_quote" style=
=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; p=
adding-left: 1ex;">
However, even that seems to have a much higher cost than I'd expect:<br><br=
>&nbsp;&nbsp; lab.devel.configdb=3D# explain delete from &quot;tmp_table2&q=
uot; where id in<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(select=
 id from tmp_table2 where name=3D'obsid' and value =3D 'oid080505');
<br>&nbsp;&nbsp; NOTICE:&nbsp;&nbsp;QUERY PLAN:<br><br>&nbsp;&nbsp; Seq Sca=
n on tmp_table2&nbsp;&nbsp;(cost=3D0.00..65705177237.26 rows=3D769844 width=
=3D6)<br>&nbsp;&nbsp;&nbsp;&nbsp; SubPlan<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp; -&gt;&nbsp;&nbsp;Materialize&nbsp;&nbsp;(cost=3D42674.32..42674.32 r=
ows=3D38 width=3D50)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp;&nbsp;Seq Scan on tmp_table2&nbsp;&nbsp;(cost=3D0.00..42674.32
rows=3D38 width=3D50)<br></blockquote></div><br>
For one reason or the other, the planner things a sequential scan is
the best solution. Try turning off seq_scan before the query and see if
it changes the plan (set enable_seqscan off;).&nbsp; <br>
<br>
I've seen this problem with sub queries and that usually solves it.<br>
<br>-- <br>This E-mail is covered by the Electronic Communications Privacy =
Act, 18 U.S.C. 2510-2521 and is legally privileged.<br><br>This
information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this
message is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is
strictly prohibited.

------=_Part_7809_18807905.1132013414563--