Skip site navigation (1) Skip section navigation (2)

Re: Accessing database statistics

From: "Tony Griffiths(RA)" <griffitt(at)cs(dot)man(dot)ac(dot)uk>
To: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Accessing database statistics
Date: 2002-05-30 12:51:12
Message-ID: 3CF62040.80605@cs.man.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
I tried the explain route, and it looks like the query acts by doing a 
complete scan of the table, so not very efficient. Output of the EXPLAIN 
was:

 EXPLAIN select count(*) from person;
NOTICE:  QUERY PLAN:
 
Aggregate  (cost=22.50..22.50 rows=1 width=0)
  ->  Seq Scan on person  (cost=0.00..20.00 rows=1000 width=0)
 
EXPLAIN

looks like I need to consult the system tables.

Tony


Duncan Adams (DNS) wrote:

>o yes i forgot u could try to see 
>
>EXPLAIN select count(*) from <table>;
>
>but i still think tom is u'r best bet for this.
>
>Ok, this all depends on how postgresql does this query. If it (behind 
>the scenes) does a call to a system table which holds a field for the 
>count of each table then fine - nice and efficient. However if this does 
>a scan of the appropriate table and counts the number of tuples then 
>returns this figure, then this is a really expensive operation, and I 
>need this to be fast.
>
>Tony
>
>Duncan Adams (DNS) wrote:
>
>>for the first part u might try 
>>
>>select count(*) from <table>;
>>
>>-----Original Message-----
>>From: Tony Griffiths(RA) [mailto:griffitt(at)cs(dot)man(dot)ac(dot)uk]
>>Sent: Thursday, May 30, 2002 2:01 PM
>>To: pgsql-novice(at)postgresql(dot)org
>>Subject: [NOVICE] Accessing database statistics
>>
>>
>>Hi,
>>I need to write a query that returns the number of rows currently stored 
>>in a table. I presume that I issue a query against the system tables, 
>>but don't know which one(s) to do this against. So a couple of questions:
>>
>>1) With specific reference to my problem, how do I do this?
>>2) More generally, is there any where that gives detailed descriptions 
>>of the system tables?
>>
>>Many thanks,
>>
>>
>>Tony
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>



In response to

pgsql-novice by date

Next:From: Henshall, Stuart - WCPDate: 2002-05-30 12:52:21
Subject: Re: Enabling Auditing in Postgres
Previous:From: Duncan Adams (DNS)Date: 2002-05-30 12:36:51
Subject: Re: Accessing database statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group