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