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 2DDC3D9388 for ; Tue, 20 Sep 2005 15:29:43 -0300 (ADT) 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 22271-08 for ; Tue, 20 Sep 2005 18:29:33 +0000 (GMT) Received: from presinet.com (presinet.com [209.53.156.1]) by svr1.postgresql.org (Postfix) with ESMTP id A734BD9312 for ; Tue, 20 Sep 2005 15:29:32 -0300 (ADT) Received: from [10.10.1.151] ([10.10.1.151]) by presinet.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 20 Sep 2005 11:28:09 -0700 Message-ID: <4330550B.80600@PresiNET.com> Date: Tue, 20 Sep 2005 11:29:31 -0700 From: Bricklen Anderson User-Agent: Debian Thunderbird 1.0.6 (X11/20050802) X-Accept-Language: en-us, en MIME-Version: 1.0 To: evgeny gridasov Cc: pgsql-performance@postgresql.org Subject: Re: Planner statistics vs. count(*) References: <20050920200210.27ad75cf.eugrid@fpm.kubsu.ru> <20050920221255.2c32b87b.eugrid@fpm.kubsu.ru> In-Reply-To: <20050920221255.2c32b87b.eugrid@fpm.kubsu.ru> Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 20 Sep 2005 18:28:09.0312 (UTC) FILETIME=[0D14DE00:01C5BE11] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 required=5 tests=[AWL=0.010] X-Spam-Level: X-Archive-Number: 200509/223 X-Sequence-Number: 14531 evgeny gridasov wrote: > Hi Everybody. > > I am going to replace some 'select count(*) from ... where ...' queries > which run on large tables (10M+ rows) with something like > 'explain select * from ... where ....' and parse planner output after that > to find out its forecast about number of rows the query is going to retrieve. > > Since my users do not need exact row count for large tables, this will > boost performance for my application. I ran some queries with explain and > explain analyze then. If i set statistics number for the table about 200-300 > the planner forecast seems to be working very fine. > > My questions are: > 1. Is there a way to interact with postgresql planner, other than 'explain ...'? An aggregate query like 'select estimate_count(*) from ...' would really help =)) > 2. How precise is the planner row count forecast given for a complex query (select with 3-5 joint tables,aggregates,subselects, etc...)? > > I think that this has been done before. Check the list archives (I believe it may have been Michael Fuhr?) ah, check this: http://archives.postgresql.org/pgsql-sql/2005-08/msg00046.php -- _______________________________ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. _______________________________