pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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