On Mon, 12 Oct 2009, ?? ? wrote:
> perf=# select count(*) from test;
In PostgreSQL, if you're selecting every record from the table for a count
of them, you have to visit them all no matter what. The most efficient
way to do that is with a full table scan. Using an index instead requires
more disk I/O, because you have to read both the index blocks and the disk
blocks.
> The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full
> scanning,speed quickly many than postgresql.
Some other database systems can do just an index scan instead to compute
aggregates like count, but even there the rules are pretty complicated;
http://www.jlcomp.demon.co.uk/faq/count_rows.html covers a lot of the
material there for Oracle's implementation. Unfortunately this particular
optimization isn't available in Postgres yet, and you'll only switch to an
index scan if you're running a query that only selects a small number of
records where an index on the condition you're checking for exists.
There's some information about alternative ways to solve this problem at
http://wiki.postgresql.org/wiki/Slow_Counting
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD