Postgres Pro
Downloads
Home   >   mailing lists

Re: [HACKERS] eval_const_expresisions and ScalarArrayOpExpr - Mailing list pgsql-hackers

From Dmitry Dolgov
Subject Re: [HACKERS] eval_const_expresisions and ScalarArrayOpExpr
Date
Msg-id CA+q6zcUC1qkaCgZkcLFVXpR2A-uRE0uYL2Ovrn5Yvr+uRUEL8Q@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] eval_const_expresisions and ScalarArrayOpExpr  (Michael Paquier <michael.paquier@gmail.com>)
Responses Re: [HACKERS] eval_const_expresisions and ScalarArrayOpExpr  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Tree view
> On 12 September 2017 at 15:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> This patch no longer applies cleanly on HEAD, so here's a rebased version
> (no substantive changes).  As before, I think the most useful review task
> would be to quantify whether it makes planning noticeably slower.

I tried to experiment a bit with this patch, hope it may be helpful. Basically
I've made some number of pgbench runs over an instance with/without the patch,
assuming, that since the only difference would be in the planner, this will
show performance impact from the patch. Also, I've manually collected some
statistics for "Planning time" from explain analyze (I suppose it's also a valid
test). As a test target I've used queries like `select * from test where 1 in
(1, 2)` (as a side note - an involved table was empty) for different number of
elements in an array, and for situations when this condition is true/false.

So, for:

    pgbench -c 50 -s 100 -j 50 -n -t 1000 -f const_eval.sql

I've got this data (avg latency):

type            w/ patch    w/o patch

short array     5.203       5.564

long array      9.293       9.667

Just out of curiosity I've also made the same test for constant arrays instead
of integers, but in this case numbers for with and without the patch were
almost identical.

For explain analyze (avg planning time):

type            w/ patch    w/o patch

short array     0.214       0.226

long array      0.374       0.320

I'm not sure why for the long array case with the patch I have smaller latency
(I thought, that more complexity should have negative impact on the
performance), but a bit longer planning time. But at the end it looks that
the difference is not that big.

Speaking about the code, everything looks fine. As for me, some variables could
be named in a more self-explanatory way (e.g `ece_evaluate_expr`, where `ece`
is `eval_const_expresisions`, or `saop`, which is `ScalarArrayOp`), but it's
minor.

I wonder if it makes sense in this patch also deal with the following
situation?

explain analyze select * from test where 1 in (select 1);
                                 QUERY PLAN
-----------------------------------------------------------------------------------------------
Result  (cost=0.02..35.52 rows=2550 width=4) (actual time=0.036..0.036 rows=0 loops=1)
   One-Time Filter: (hashed SubPlan 1)
   ->  Seq Scan on test  (cost=0.02..35.52 rows=2550 width=4) (actual time=0.009..0.009 rows=0 loops=1)
   SubPlan 1
->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
Planning time: 0.381 ms
Execution time: 0.360 ms

It looks quite similar from a user perspective (although since a subplan is
involved, I assume it may be quite different in terms of implementation).

pgsql-hackers by date:

Previous
From: Jeevan Chalke
Date:
Subject: Re: [HACKERS] Partition-wise aggregation/grouping
Next
From: Stephen Frost
Date:
Subject: Re: PATCH: Exclude unlogged tables from base backups