While reading [1] in context of Postgres Hibernator, I see that
Mitsuru mentioned one of the ways other RDBMS allows count(*) to be
driven by an index.
> 'select /*+ INDEX(emp emp_pk) */ count(*) from emp;' to load index blocks
I am not sure if Postgres planner already allows this, but it would be
great if the planner considered driving a count(*) query using a
non-partial index, in the hopes that it turns into an index-only scan,
and hence returns count(*) result faster.The non-partial index may not
necessarily be the primary key index, it can be chosen purely based on
size, favouring smaller indexes.
This may alleviate some of the concerns of people migrating
applications from other DBMS' that perform count(*) in a blink of an
eye.
[1]: http://www.postgresql.org/message-id/20110507.022228.83883502.iwasaki@jp.FreeBSD.org
Best regards,
PS: Please note that I am not proposing to add support for the
optimizer hint embedded in Mitsuru's query.
--
Gurjeet Singh http://gurjeet.singh.im/
EDB www.EnterpriseDB.com