Thread: rows in explain

On Fri, Jul 29, 2005 at 11:54:18AM -0600, Lee Wu wrote:
> explain select count(*) from pg_class where relname='pg_class';
>                           QUERY PLAN
> --------------------------------------------------------------
>  Aggregate  (cost=4.55..4.55 rows=1 width=0)
>    ->  Seq Scan on pg_class  (cost=0.00..4.55 rows=1 width=0)
>          Filter: (relname = 'pg_class'::name)
> (3 rows)
>
> Where and how PG get "rows=1"?

The planner uses statistics to estimate the number of rows that a
query will return.  Here are a couple of links that provide more
information:

http://www.postgresql.org/docs/8.0/static/planner-stats.html
http://www.postgresql.org/docs/8.0/static/view-pg-stats.html
http://www.postgresql.org/docs/8.0/static/sql-analyze.html
http://developer.postgresql.org/docs/postgres/planner-stats-details.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael,

Thanks a lot.
http://developer.postgresql.org/docs/postgres/planner-stats-details.html
is very useful for me.

However, if I can be provided some specific functions to get rows count,
That will be greatly appreciated.

Basically, I want to query data dictionary to get the estimated
number of rows rather than from the underlying table since the query on
the table is too expensive for me.

Specifically, my current query looks like:
select count(*) from a_big_table where a_no_unique_index = 123;
(sometimes, index scan, sometimes, seq scan, neither is acceptable).

I need a query like:
select A_PG_FUNCTION('select count(*) from a_big_table where
a_no_unique_index = 123');

while A_PG_FUNCTION or MANY_PG_FUNCTIONS is/are used to get rows when
a query is explained.

Otherwise, I can write my own function, but do not want to
Reinvent the wheel.

Have a good weekend!


-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Friday, July 29, 2005 12:45 PM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] rows in explain

On Fri, Jul 29, 2005 at 11:54:18AM -0600, Lee Wu wrote:
> explain select count(*) from pg_class where relname='pg_class';
>                           QUERY PLAN
> --------------------------------------------------------------
>  Aggregate  (cost=4.55..4.55 rows=1 width=0)
>    ->  Seq Scan on pg_class  (cost=0.00..4.55 rows=1 width=0)
>          Filter: (relname = 'pg_class'::name)
> (3 rows)
>
> Where and how PG get "rows=1"?

The planner uses statistics to estimate the number of rows that a
query will return.  Here are a couple of links that provide more
information:

http://www.postgresql.org/docs/8.0/static/planner-stats.html
http://www.postgresql.org/docs/8.0/static/view-pg-stats.html
http://www.postgresql.org/docs/8.0/static/sql-analyze.html
http://developer.postgresql.org/docs/postgres/planner-stats-details.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/