pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
rows in explain
3+ messages / 2 participants
[nested] [flat]

* rows in explain
@ 2005-07-29 17:54 Lee Wu <Lwu@mxlogic.com>
  2005-07-29 18:45 ` Re: rows in explain Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Lee Wu @ 2005-07-29 17:54 UTC (permalink / raw)
  To: pgsql-admin

Hi all,

 

I want to find out how PG gets rows when it explains a query.

For example, when I issue:

 

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"?

 

Thank you!

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

* Re: rows in explain
  2005-07-29 17:54 rows in explain Lee Wu <Lwu@mxlogic.com>
@ 2005-07-29 18:45 ` Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael Fuhr @ 2005-07-29 18:45 UTC (permalink / raw)
  To: Lee Wu <Lwu@mxlogic.com>; +Cc: pgsql-admin

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/



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

* Re: rows in explain
@ 2005-07-29 20:48 Lee Wu <Lwu@mxlogic.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Lee Wu @ 2005-07-29 20:48 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin

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/



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


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

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-29 17:54 rows in explain Lee Wu <Lwu@mxlogic.com>
2005-07-29 18:45 ` Michael Fuhr <mike@fuhr.org>
2005-07-29 20:48 Re: rows in explain Lee Wu <Lwu@mxlogic.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