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.adams@vcontractor.co.za]
> Sent: 30 May 2002 13:37
> To: 'Tony Griffiths(RA)'; pgsql-novice@postgresql.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@cs.man.ac.uk]
> >Sent: Thursday, May 30, 2002 2:01 PM
> >To: pgsql-novice@postgresql.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
>
