Expr. extended stats are skipped with equality operator

From: Danny Shemesh <dany74q(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Expr. extended stats are skipped with equality operator
Date: 2022-08-05 09:50:53
Message-ID: CAFZC=QqfpQCG4gCsZYnYgDseazjiKNLM3=vUg3zS64LbuXrzvQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
<https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5379>)
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
<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
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aleš Zelený 2022-08-05 15:47:02 Re: PostgreSQL 14.4 ERROR: out of memory issues
Previous Message Peter Smith 2022-08-05 06:03:29 Re: Support logical replication of DDLs