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@xxxxxxxx] Sent: Friday, July 29, 2005 12:45 PM To: Lee Wu Cc: pgsql-admin@xxxxxxxxxxxxxx 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/
