X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 23F439FB2FB
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 31 Jul 2006 13:10:20 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 02458-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 31 Jul 2006 13:10:03 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from web88007.mail.re2.yahoo.com (web88007.mail.re2.yahoo.com
	[206.190.37.194])
	by postgresql.org (Postfix) with SMTP id 4CA019FA26E
	for <pgsql-performance@postgresql.org>;
	Mon, 31 Jul 2006 13:09:18 -0300 (ADT)
Received: (qmail 37304 invoked by uid 60001); 31 Jul 2006 16:09:02 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=rogers.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=hRxLRCQPpDwGdMOPyziSLyaJZam4DdEKYlIItXvbJ2yjol4AXvS30Xs9G6c6vMizYIN3HvkSLmEgx7qjyYbb89SWYUb9p7JyYRWKJw8lRkm1RJwO41J6tyyux7b8qmyRWQ1QJNP7ZMuZN7jo+EbNQewmfsTn++Pq/zI+guLHwwY=
	;
Message-ID: <20060731160902.37302.qmail@web88007.mail.re2.yahoo.com>
Received: from [216.16.241.206] by web88007.mail.re2.yahoo.com via HTTP;
	Mon, 31 Jul 2006 12:09:02 EDT
Date: Mon, 31 Jul 2006 12:09:02 -0400 (EDT)
From: H Hale <hhale21@rogers.com>
Subject: Re: sub select performance due to seq scans 
To: pgsql-performance@postgresql.org
In-Reply-To: <14463.1154356126@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-151137922-1154362142=:34229"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=4.004 tagged_above=0 required=5 tests=AWL,
	HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL
X-Spam-Level: ****
X-Archive-Number: 200607/317
X-Sequence-Number: 20177

--0-151137922-1154362142=:34229
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Tom, 
 
 It is unique. 

 Indexes:
     "flatomfilesysentry_pkey" PRIMARY KEY, btree (objectid)
     "capsa_flatomfilesysentry_name_idx" btree (name)
 Foreign-key constraints:
     "objectid" FOREIGN KEY (objectid) REFERENCES capsa_sys.master(objectid) ON DELETE CASCADE
 
 
Tom Lane <tgl@sss.pgh.pa.us> wrote: H Hale  writes:
> -> Bitmap Heap Scan on flatomfilesysentry (cost=2.00..274.38 rows=3238 width=30) (actual time=0.011..0.013 rows=1 loops=6473)
>           Recheck Cond: (flatomfilesysentry.objectid = "outer".dstobj)
> -> Bitmap Index Scan on flatomfilesysentry_pkey (cost=0.00..2.00 rows=3238 width=0) (actual time=0.007..0.007 rows=1 loops=6473)
>                 Index Cond: (flatomfilesysentry.objectid = "outer".dstobj)

Well, there's our estimation failure: 3238 rows expected, one row
actual.

What is the data distribution of flatomfilesysentry.objectid?
It looks from this example like it is unique or nearly so,
but the planner evidently does not think that.

   regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


--0-151137922-1154362142=:34229
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Tom, <br> <br> It is unique. <br><br> Indexes:<br> &nbsp;&nbsp;&nbsp; "flatomfilesysentry_pkey" PRIMARY KEY, btree (objectid)<br> &nbsp;&nbsp;&nbsp; "capsa_flatomfilesysentry_name_idx" btree (name)<br> Foreign-key constraints:<br> &nbsp;&nbsp;&nbsp; "objectid" FOREIGN KEY (objectid) REFERENCES capsa_sys.master(objectid) ON DELETE CASCADE<br> <br> <br><b><i>Tom Lane &lt;tgl@sss.pgh.pa.us&gt;</i></b> wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> H Hale <hhale21 @rogers.com=""> writes:<br>&gt; -&gt; Bitmap Heap Scan on flatomfilesysentry (cost=2.00..274.38 rows=3238 width=30) (actual time=0.011..0.013 rows=1 loops=6473)<br>&gt;           Recheck Cond: (flatomfilesysentry.objectid = "outer".dstobj)<br>&gt; -&gt; Bitmap Index Scan on flatomfilesysentry_pkey (cost=0.00..2.00 rows=3238 width=0) (actual time=0.007..0.007 rows=1 loops=6473)<br>&gt;                 Index Cond: (flatomfilesysentry.objectid =
 "outer".dstobj)<br><br>Well, there's our estimation failure: 3238 rows expected, one row<br>actual.<br><br>What is the data distribution of flatomfilesysentry.objectid?<br>It looks from this example like it is unique or nearly so,<br>but the planner evidently does not think that.<br><br>   regards, tom lane<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 5: don't forget to increase your free space map settings<br></hhale21></blockquote><br>
--0-151137922-1154362142=:34229--