Re: Runtime pruning problem

Hi,

On 2019/04/16 21:09, David Rowley wrote:
> On Tue, 16 Apr 2019 at 23:55, Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> postgres=# explain analyze select * from t1 where dt = current_date + 400;
>> QUERY PLAN
>> ---------------------------------------------------------------------------------------
>> Append (cost=0.00..198.42 rows=44 width=8) (actual time=0.000..0.001 rows=0 loops=1)
>> Subplans Removed: 3
>> -> Seq Scan on t1_1 (cost=0.00..49.55 rows=11 width=8) (never executed)
>> Filter: (dt = (CURRENT_DATE + 400))
>> Planning Time: 0.400 ms
>> Execution Time: 0.070 ms
>> (6 rows)
>> ----
>>
>> I realized t1_1 was not scanned actually since "never executed"
>> was displayed in the plan using EXPLAIN ANALYZE. But I think
>> "One-Time Filter: false" and "Subplans Removed: ALL" or something
>> like that should be displayed instead.
>>
>> What do you think?
>
> This is intended behaviour explained by the following comment in nodeAppend.c
>
> /*
> * The case where no subplans survive pruning must be handled
> * specially. The problem here is that code in explain.c requires
> * an Append to have at least one subplan in order for it to
> * properly determine the Vars in that subplan's targetlist. We
> * sidestep this issue by just initializing the first subplan and
> * setting as_whichplan to NO_MATCHING_SUBPLANS to indicate that
> * we don't really need to scan any subnodes.
> */
>
> It's true that there is a small overhead in this case of having to
> initialise a useless subplan, but the code never tries to pull any
> tuples from it, so it should be fairly minimal. I expected that using
> a value that matches no partitions would be unusual enough not to go
> contorting explain.c into working for this case.

When I saw this, I didn't think as much of the overhead of initializing a
subplan as I was surprised to see that result at all.

When you see this:

explain select * from t1 where dt = current_date + 400;
QUERY PLAN
────────────────────────────────────────────────────────────
Append (cost=0.00..198.42 rows=44 width=8)
Subplans Removed: 3
-> Seq Scan on t1_1 (cost=0.00..49.55 rows=11 width=8)
Filter: (dt = (CURRENT_DATE + 400))
(4 rows)

Doesn't this give an impression that t1_1 *matches* the WHERE condition
where it clearly doesn't? IMO, contorting explain.c to show an empty
Append like what Hosoya-san suggests doesn't sound too bad given that the
first reaction to seeing the above result is to think it's a bug of
partition pruning.

Thanks,
Amit