Re: Using Index-only scans to speed up count(*)

Le samedi 7 juin 2014 08:35:27 Gurjeet Singh a écrit :
> 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.

IIRC it is not (yet) possible to switch from index-scan to indexonly-
scan on the fly because the structure used are different (indexonly scan
needs to prepare a tuple struct to hold data, I'm not sure of the
details).
Indexonly scan is already used to answer count(*) but decision is done
during planning.

Now, it happens that this is an interesting idea which has already been
discussed if not on postgresql-hacker at least during pre/post-
conferences social events: being able to switch the plan during
execution if things are not working as expected (in the same topic you
have 'progress bar' for query execution, at least some mechanisms should
be shared by both features).

> PS: Please note that I am not proposing to add support for the
> optimizer hint embedded in Mitsuru's query.

:-)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation