> The nastiness level of this seems quite low, compared to what happens to
> this other example if we didn't handle these easy cases:
>
> create table t (a float);
> select i from t where i in (1, 2);
> select i from t where i in (1, '2');
> select i from t where i in ('1', 2);
> select i from t where i in ('1', '2');
> select i from t where i in (1.0, 1.0);
>
> (The point here is that the datatype differs for the constants from the
> lexer down in each of these cases.)
>
> I think it's more important to handle this better than what the posted
> patch does, than improving the lexing in presence of other lexical
> elements in the query. With the current patch I get _five_
> pg_stat_statements entries from these queries above, where only one of
> them was able to apply merging of the elements:
I want to throw out an idea I was looking at today, if I may, and
apologies if this
was brought up earlier. The approachI am looking at is to have a
custom jumbling
function for ArrayExpr to handle FuncExpr and Const. The simple Const
case is quite simple,
but in the case of FuncExpr we can traverse the "args" to find the constants
and simple record the location. I don't think this adds extra overhead as
this work to traverse the "args" must already occur.
I see a few benefits with this approach:
1/ It deals with the different constant types in the IN-LIST
2/ It also deals with an IN-LIST that contains a subquery with an IN-LIST. Maybe
I am wrong, but the current patch does not support this case.
The negative aspect of this patch is the custom jumbling, but we have
examples of that for other expression types.
select pg_stat_statements_reset();
drop table if exists foo;
create table foo (col_int int, col_smallint smallint, col_bigint
bigint, col_float float, col_text text, col_varchar varchar);
select from foo where col_int in (1, 2, 3);
select from foo where col_int in (1, 2, 3, 4);
select from foo where col_int in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, '11');
select from foo where col_bigint in (1, 2, 3);
select from foo where col_bigint in (1, 2, 3, 4);
select from foo where col_bigint in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, '11');
select from foo where col_int in ((select 1 from foo where col_int in
(1, 2, 3, 4, 5)));
select from foo where col_int in ((select 1 from foo where col_int in
(1, 2, 3, 4, 5, 6, '7')));
select from foo where col_bigint in ((select 1 from foo where col_int
in (1, 2, 3, 4, 5)));
select from foo where col_bigint in ((select 1 from foo where col_int
in (1, 2, 3, 4, 5, 6, '7')));
select query, queryid, calls from pg_stat_statements where query like
'select from foo where%' order by stats_since asc;
query
| queryid | calls
--------------------------------------------------------------------------------------------------+----------------------+-------
select from foo where col_int in ($1, $2, $3)
| -8353422683112638164 | 3
select from foo where col_bigint in ($1, $2, $3)
| -1245684714076243746 | 3
select from foo where col_int in ((select $1 from foo where col_int
in ($2, $3, $4, $5, $6))) | 6337165648188018609 | 2
select from foo where col_bigint in ((select $1 from foo where
col_int in ($2, $3, $4, $5, $6))) | -1521638960953725050 | 2
(4 rows)
I will park the poc of the idea here. It does not deal with the presentation
as the current patch, but maybe it could be something to work from.
Regards,
Sami
experiment_custom_array_expr.patch
Description: Binary data
