Hey all !
I'm on a quest to help the planner (on pg14) use the best of several partial, expressional indices we have on some large tables (few TBs in size, billions of records).
As we know, stats for expressions in partial indices aren't gathered by default - so I'm tinkering with expressional extended stats to cover for those.
I've tackled two interesting points there:
1. Seems like expressional stats involving the equality operator are skipped or mismatched (
fiddle)
Let's take the following naive example:
create table t1 (x integer[]);
insert into t1 select array[1]::integer[] from generate_series(1, 100000, 1);
create statistics s1 on (x[1] = 1) from t1;
analyze t1;
explain analyze select * from t1 where x[1] = 1;
> Seq Scan on t1 (cost=0.00..1986.00 rows=500 width=29) (actual time=0.009..36.035 rows=100000 loops=1)
Now, of course one can just create the stat on x[1] directly in this case, but I have a more complex use case where an equality operator is beneficial;
should the above case be supported ? feels like I'm just missing something fundamental.
2. Less important, just a minor note - feel free to ignore - although the eq. operator above seems to be skipped when matching the ext. stats, I can work around this by using a CASE expression (
fiddle);
Building on the above example, we can:
create statistics s2 on (case x[1] when 1 then true else false end) from t1;
explain analyze select * from t1 where (case x[1] when 1 then true else false end
> Seq Scan on t1 (cost=0.00..1986.00 rows=100000 width=25) (actual time=0.011..33.721 rows=100000 loops=1)
What's a bit problematic here, though, is that if we mix other dependent columns to the extended stat, and specifically if we create an mcv,
queries involving the CASE expression throw with `error: unknown clause type 130`, where clause type == T_CaseExpr.
The second point for me would be that I've found it a bit non intuitive that creating an extended statistic can fail queries at query time; it makes sense that the mcv wouldn't work for case expressions, but it might've been a bit clearer to:
a. Fail this at statistic creation time, potentially, or
b. Convert the type numeric in the above error to its text representation, if we can extract it out at runtime somehow -
I couldn't find a mapping of clause type numerics to their names, and as the node tags are generated at compile time, it could be build-dependent and a bit hard to track down if one doesn't control the build flags
Thanks a ton for your help - appreciate your time,
Danny
