Re: table full scan or index full scan?

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