Re: Accessing database statistics

count(*) does a scan of the appropriate table.
If you really need a fast count you could try having a trigger update a row
in another
table to +1 every time a row is inserted and -1 every time a row deleted.
However this
could lead to uneeded contention. It is also worth considering the case of a
yet to be
commited transaction that has inserted/deleted having a different count to a
one that
does not. The triggers should handle this correctly, but not tried it
myself.
hth,
- Stuart

> -----Original Message-----
> From: Duncan Adams (DNS) [mailto:duncan(dot)adams(at)vcontractor(dot)co(dot)za]
> Sent: 30 May 2002 13:37
> To: 'Tony Griffiths(RA)'; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Accessing database statistics
>
>
> 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
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>