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: (view raw, whole thread or download thread mbox)
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 

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

looks like I need to consult the system tables.


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.
>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
>>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,
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?

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-2017 The PostgreSQL Global Development Group