[Postgres-xl-developers] Make ANALYZE to collect statistics about indexes too, not just tables
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:
> 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.
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
More information about the Postgres-xl-developers