pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Making SELECT COUNT(seed) FROM fast
2+ messages / 2 participants
[nested] [flat]

* Making SELECT COUNT(seed) FROM fast
@ 2001-04-11 17:58  Gerald Gutierrez <gutz@kalador.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Gerald Gutierrez @ 2001-04-11 17:58 UTC (permalink / raw)
  To: pgsql-sql

Hi all.

I have a table with about 5 million rows in it. I need to be able to get the
exact number of rows in the table at runtime. So I tried the following:

xxx=> explain select count(seed) from mytable;
NOTICE:  QUERY PLAN:

Aggregate  (cost=103152.27..103152.27 rows=1 width=4)
  ->  Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4)

EXPLAIN
xxx=>

Actually executing this query takes about 2 minutes on a P3-800MHz machine
with 512MB of RAM.

I have an index on the seed table, and I have done VACUUM ANALYZE on the
table after inserting the rows. Is there any way I can get this to be fast?

Thanks.


Gerald.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Making SELECT COUNT(seed) FROM fast
@ 2001-04-11 18:30  Peter Eisentraut <peter_e@gmx.net>
  parent: Gerald Gutierrez <gutz@kalador.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Eisentraut @ 2001-04-11 18:30 UTC (permalink / raw)
  To: Gerald Gutierrez <gutz@kalador.com>; +Cc: pgsql-sql

Gerald Gutierrez writes:

> I have a table with about 5 million rows in it. I need to be able to get the
> exact number of rows in the table at runtime. So I tried the following:
>
> xxx=> explain select count(seed) from mytable;
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=103152.27..103152.27 rows=1 width=4)
>   ->  Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4)

> Actually executing this query takes about 2 minutes on a P3-800MHz machine
> with 512MB of RAM.
>
> I have an index on the seed table, and I have done VACUUM ANALYZE on the
> table after inserting the rows. Is there any way I can get this to be fast?

For a count of all rows you necessarily need to visit all rows (at least
in this implementation), so an index is of no use.  For a sequential scan
with little computation involved this really comes down to pure hardware
speed.  You might be able to speed it up a little by using count(*)
instead.  Note that there's a semantic difference, because count(seed)
doesn't count the rows where seed is null, which is probably not what you
intended anyway.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-04-11 18:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-11 17:58 Making SELECT COUNT(seed) FROM fast Gerald Gutierrez <gutz@kalador.com>
2001-04-11 18:30 ` Peter Eisentraut <peter_e@gmx.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