pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCounting all rows
4+ messages / 3 participants
[nested] [flat]
* Counting all rows
@ 2007-06-23 10:15 Stefan Arentz <stefan.arentz@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Stefan Arentz @ 2007-06-23 10:15 UTC (permalink / raw)
To: pgsql-sql
I need to get statistics from a bunch of tables. Simply the number of
records in them.
The query plan looks like this:
=> explain select count(id) from stuff;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=1629.69..1629.70 rows=1 width=8)
-> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8)
The sequential scan kind of worries me. Is there a better/faster way to do this?
S.
S.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Counting all rows
@ 2007-06-23 10:38 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Stefan Arentz <stefan.arentz@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Kretschmer @ 2007-06-23 10:38 UTC (permalink / raw)
To: pgsql-sql
Stefan Arentz <stefan.arentz@gmail.com> schrieb:
> I need to get statistics from a bunch of tables. Simply the number of
> records in them.
>
> The query plan looks like this:
>
> => explain select count(id) from stuff;
> QUERY PLAN
> -------------------------------------------------------------------
> Aggregate (cost=1629.69..1629.70 rows=1 width=8)
> -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8)
>
> The sequential scan kind of worries me. Is there a better/faster way to do
> this?
Yes.
A "select count(*) from foo;" enforces a seq. scan. Solutions for this
are discussed here: http://www.varlena.com/GeneralBits/120.php
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Counting all rows
@ 2007-06-23 13:15 Stefan Arentz <stefan.arentz@gmail.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 4+ messages in thread
From: Stefan Arentz @ 2007-06-23 13:15 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
On 6/23/07, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Stefan Arentz <stefan.arentz@gmail.com> schrieb:
>
> > I need to get statistics from a bunch of tables. Simply the number of
> > records in them.
> >
> > The query plan looks like this:
> >
> > => explain select count(id) from stuff;
> > QUERY PLAN
> > -------------------------------------------------------------------
> > Aggregate (cost=1629.69..1629.70 rows=1 width=8)
> > -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8)
> >
> > The sequential scan kind of worries me. Is there a better/faster way to do
> > this?
>
> Yes.
>
> A "select count(*) from foo;" enforces a seq. scan. Solutions for this
> are discussed here: http://www.varlena.com/GeneralBits/120.php
Thank you Andreas. That is exactly what I was looking for.
S.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Counting all rows
@ 2007-06-23 15:13 manchicken <manchicken@notsosoft.net>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 4+ messages in thread
From: manchicken @ 2007-06-23 15:13 UTC (permalink / raw)
To: pgsql-sql
On Saturday 23 June 2007 05:38:56 Andreas Kretschmer wrote:
> Stefan Arentz <stefan.arentz@gmail.com> schrieb:
> > I need to get statistics from a bunch of tables. Simply the number of
> > records in them.
> >
> > The query plan looks like this:
> >
> > => explain select count(id) from stuff;
> > QUERY PLAN
> > -------------------------------------------------------------------
> > Aggregate (cost=1629.69..1629.70 rows=1 width=8)
> > -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8)
> >
> > The sequential scan kind of worries me. Is there a better/faster way to
> > do this?
>
> Yes.
>
> A "select count(*) from foo;" enforces a seq. scan. Solutions for this
> are discussed here: http://www.varlena.com/GeneralBits/120.php
>
>
> Andreas
Is there any neat trick for doing this for paging estimates? It looks like
the only way to get a result count from a more complex query is to either
fetch a count first, or to fetch all records and count them in the
application.
--
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-06-23 15:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-23 10:15 Counting all rows Stefan Arentz <stefan.arentz@gmail.com>
2007-06-23 10:38 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-06-23 13:15 ` Stefan Arentz <stefan.arentz@gmail.com>
2007-06-23 15:13 ` manchicken <manchicken@notsosoft.net>
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