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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
