[Postgres-xl-developers] Make ANALYZE to collect statistics about indexes too, not just tables

Tomas Vondra tomas.vondra at 2ndquadrant.com
Thu Oct 19 09:02:59 PDT 2017



On 07/31/2017 09:44 AM, Pavan Deolasee wrote:
> 
> 
> On Mon, Jul 31, 2017 at 6:46 AM, Tomas Vondra
> <tomas.vondra at 2ndquadrant.com <mailto:tomas.vondra at 2ndquadrant.com>> wrote:
> 
>     Hi,
> 
>     One thing I realized when fixing extended statistics (commit
>     6d4a2588c8) is that we only collect statistics about the table, but
>     we entirely ignore statistics about indexes on the table. That's a
>     bit unfortunate as expression indexes are about the only way to
>     provide statistics about expressions.
> 
>     For example if you create an index like this:
> 
>         CREATE INDEX ON t ((abs(a)));
> 
>     then the optimizer can use the statistics to accurately estimate
>     selectivity of conditions like this one
> 
>         SELECT * FROM t WHERE abs(a) < 100
> 
>     while without the index it'd have to rely on some hard-coded default
>     statistics (which is 33% for inequalities, IIRC).
> 
>     So this is fairly annoying, and there are multiple regression tests
>     relying on this, in fact.
> 
>     The attached patch addresses this by minor refactoring of the
>     ANALYZE code, and then collecting statistics for all indexes on the
>     table, in exactly the same way we do that for a table. Luckily,
>     statistics for indexes are stored in exactly the same catalog as
>     table stats, so we don't need to do anything special.
> 
> 
> Seems reasonable to me.
> 

I've pushed this today, with some minor improvements.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


More information about the Postgres-xl-developers mailing list