Re: Possible improvement - Mailing list pgsql-general

On Fri, 5 Jun 2020 at 14:41, Paul van der Linden
<paul.doskabouter@gmail.com> wrote:
> If I have a query like:
>
> SELECT * FROM (
> SELECT
>   CASE
>       WHEN field='value1' THEN 1
>       WHEN field='value2' THEN 2
>   END AS category
> FROM table1
> ) AS foo
> WHERE category=1
>
> doesn't use the index on field, while technically it could do that.
> Is it hard to implement drilling down the constant in the WHERE to within the CASE?

It doesn't look impossible to improve that particular case.  See
eval_const_expressions_mutator() in clauses.c at T_CaseExpr. However,
this would need to take constant folding further than we take it
today. Today we just have the ability to simplify expressions which
are, by themselves, an expression which will always evaluate to a
constant value. This case is more complex as it requires something
outside the CASE expr to allow the simplification to take place. In
this case, we'd need to look at the other side of the OpExpr to see
the const there before any transformation could simplify it.  It's
also not entirely clear that the simplification would always be a good
idea.  What, for example if there was an index on the case statement
but none on "field". The query may perform worse!  The unfortunate
part about this is that, generally, when we perform constant folding,
we don't yet have an idea about which indexes exist.  I imagine the
only sane way to do it would be to allow expressions to have some sort
of "alternative" expression that could be matched up to the index
column instead. It wouldn't be a trivial piece of work to do that.

For the more simple cases, you can see from looking at:

postgres=# explain select * from pg_class where oid = (case when
'test' = 'test' then 1 else 0 end);
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.29
rows=1 width=260)
   Index Cond: (oid = '1'::oid)
(2 rows)

that we do simplify case statements which are by themselves constant.

> Is this something that could be put on some wishlist? If so where are the most looked at ones?

There is a todo list of sorts in [1]. However, I'm really unsure if
anyone ever looks at it for something to do. Mostly, people have their
own ideas and problems to solve and spend their free cycles hacking
away at those. You might have equal luck waiting until December and
writing it on a piece of paper and setting it on fire. Likely there
would be more chance if it was something simple as a novice who's
looking into getting into working on Postgres might skim that list for
something to work on.  More experienced people, I imagine, would never
look there.  FWIW, many people who are now working on PostgreSQL once
came along with a question or idea like yours. Many have been unable
to escape ever since :)

David

[1] https://wiki.postgresql.org/wiki/Todo