Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 12B102E35EC for ; Wed, 12 Dec 2007 10:30:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 21764-07 for ; Wed, 12 Dec 2007 10:29:53 -0400 (AST) Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by postgresql.org (Postfix) with ESMTP id B0CA32E3571 for ; Wed, 12 Dec 2007 10:30:01 -0400 (AST) Received: from [192.168.2.102] ([71.100.89.137]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JSX0048WXLPOHT3@vms042.mailsrvcs.net> for pgsql-sql@postgresql.org; Wed, 12 Dec 2007 08:29:50 -0600 (CST) Date: Wed, 12 Dec 2007 09:29:49 -0500 From: Gerry Reno Subject: Re: join on three tables is slow In-reply-to: <11806.1197434157@sss.pgh.pa.us> To: pgsql-sql@postgresql.org Message-id: <475FF05D.9040009@verizon.net> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <475C96EA.6090002@verizon.net> <475CC478.7010102@Sheeky.Biz> <475E111B.6060203@verizon.net> <162867790712102200m2144573bw7447e0eff6720d55@mail.gmail.com> <475E8F6A.4030004@verizon.net> <162867790712110655o2cf99bf1n9acf6b440d726440@mail.gmail.com> <475ED469.5080906@verizon.net> <11806.1197434157@sss.pgh.pa.us> User-Agent: Thunderbird 1.5.0.12 (X11/20070530) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/68 X-Sequence-Number: 29760 Tom Lane wrote: > Gerry Reno writes: > >> Pavel Stehule wrote: >> >>> there is diference in agg position. Send, please, query and explain >>> analyze output. >>> > > [ explain analyze output ] > > The rowcount estimates seem pretty far off, even for simple cases that > I'd expect it to get right, eg > > >> -> Seq Scan on res_partner_address a (cost=0.00..88.40 >> rows=16 width=552) (actual time=0.851..16.131 rows=559 loops=1) >> Filter: ((("type")::text = 'default'::text) OR ("type" IS >> NULL)) >> > > Are the ANALYZE stats up to date for these tables? > > What PG version is this, anyway? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -bash-3.2$ yum list postgresql Loading "installonlyn" plugin Installed Packages postgresql.i386 8.2.4-1.fc7 installed You were right on the money Tom. I vacuumed the database and now the query with the boolean executes in only 50% more time than without. About 15 secs instead of 10 secs. Big improvement. I hadn't seen a vacuum produce this much of an improvement in performance before. Thanks, Gerry