On Fri, Aug 05, 2022 at 04:43:36PM +0300, Danny Shemesh wrote: > 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 > <https://www.db-fiddle.com/f/wJZNH1rNwJSo3D5aByQiWX/1>); > 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 A reproducer for this: CREATE TABLE t1(x int[], y float); INSERT INTO t1 SELECT array[1], a FROM generate_series(1,99)a; CREATE STATISTICS s2 ON (CASE x[1] WHEN 1 THEN true ELSE false END), y FROM t1; ANALYZE t1; explain analyze SELECT * FROM t1 WHERE CASE x[1] WHEN 1 THEN true ELSE false END AND y=1; ERROR: unknown clause type: 134 \errverbose ERROR: XX000: unknown clause type: 134 LOCATION: mcv_get_match_bitmap, mcv.c:1950 I'm not sure what Tomas will say, but XX000 errors from elog() are internal and not intended to be user-facing, which is why there's no attempt to output a friendly clause name. It might be that this wasn't reachable until statistics on expressions were added in v14. -- Justin
