út 28. 4. 2020 v 16:48 odesílatel Tomas Vondra <tomas.von...@2ndquadrant.com> napsal:
> On Tue, Apr 28, 2020 at 03:43:43PM +0200, Pavel Stehule wrote: > >út 28. 4. 2020 v 15:26 odesílatel Tomas Vondra < > tomas.von...@2ndquadrant.com> > >napsal: > > > >> ... > >> > >> >I'm not so concerned about this in any query where we have a real FROM > >> >clause because even if we end up with only one row, the relative > >> >penalty is low, and the potential gain is very high. But simple > >> >expressions in pl/pgsql, for example, are a case where we can know for > >> >certain (correct me if I've wrong on this) that we'll only execute the > >> >expression once, which means there's probably always a penalty for > >> >choosing the implementation with setup costs over the default linear > >> >scan through the array. > >> > > >> > >> What do you mean by "simple expressions"? I'm not plpgsql expert and I > >> see it mostly as a way to glue together SQL queries, but yeah - if we > >> know a given ScalarArrayOpExpr will only be executed once, then we can > >> disable this optimization for now. > >> > > > >a := a + 1 > > > >is translated to > > > >SELECT $1 + 1 and save result to var a > > > >The queries like this "SELECT $1 + 1" are simple expressions. They are > >evaluated just on executor level - it skip SPI > > > >the simple expression has not FROM clause, and have to return just one > row. > >I am not sure if it is required, it has to return just one column. > > > >I am not sure if executor knows so expression is executed as simply > >expressions. But probably it can be deduced from context > > > > Not sure. The executor state is created by exec_eval_simple_expr, which > calls ExecInitExprWithParams (and it's the only caller). And that in > turn is the only place that leaves (state->parent == NULL). So maybe > that's a way to identify simple (standalone) expressions? Otherwise we > might add a new EEO_FLAG_* to identify these expressions explicitly. > > I wonder if it would be possible to identify cases when the expression > is executed in a loop, e.g. like this: > > FOR i IN 1..1000 LOOP > x := y IN (1, 2, ..., 999); > END LOOP; > > in which case we only build the ScalarArrayOpExpr once, so maybe we > could keep the hash table for all executions. But maybe that's not > possible or maybe it's pointless for other reasons. It sure looks a bit > like trying to build a query engine from FOR loop. > Theoretically it is possible, not now. But I don't think so it is necessary. I cannot to remember this pattern in any plpgsql code and I never seen any request on this feature. I don't think so this is task for plpgsql engine. Maybe for JIT sometimes. > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
