pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Planner statistics vs. count(*)
2+ messages / 2 participants
[nested] [flat]

* Planner statistics vs. count(*)
@ 2005-09-20 18:12  evgeny gridasov <eugrid@fpm.kubsu.ru>
  0 siblings, 1 reply; 2+ messages in thread

From: evgeny gridasov @ 2005-09-20 18:12 UTC (permalink / raw)
  To: pgsql-performance

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...)?


-- 
Evgeny Gridasov
Software Developer
I-Free, Russia



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Planner statistics vs. count(*)
@ 2005-09-20 18:29  Bricklen Anderson <BAnderson@PresiNET.com>
  parent: evgeny gridasov <eugrid@fpm.kubsu.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Bricklen Anderson @ 2005-09-20 18:29 UTC (permalink / raw)
  To: evgeny gridasov <eugrid@fpm.kubsu.ru>; +Cc: pgsql-performance

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.
_______________________________



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-09-20 18:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-20 18:12 Planner statistics vs. count(*) evgeny gridasov <eugrid@fpm.kubsu.ru>
2005-09-20 18:29 ` Bricklen Anderson <BAnderson@PresiNET.com>

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