From: Bricklen Anderson <BAnderson@PresiNET.com>
To: evgeny gridasov <eugrid@fpm.kubsu.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner statistics vs. count(*)
Date: Tue, 20 Sep 2005 11:29:31 -0700
Message-ID: <4330550B.80600@PresiNET.com> (raw)
In-Reply-To: <20050920221255.2c32b87b.eugrid@fpm.kubsu.ru>
References: <A209FE4DA934614CAF3F5BD8E5E14290B0DD8D@ex2k.bankofamerica.com>
<20050920200210.27ad75cf.eugrid@fpm.kubsu.ru>
<20050920221255.2c32b87b.eugrid@fpm.kubsu.ru>
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.
_______________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: BAnderson@PresiNET.com, eugrid@fpm.kubsu.ru
Subject: Re: Planner statistics vs. count(*)
In-Reply-To: <4330550B.80600@PresiNET.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox