I spent some more time poking at Jaime's example. I can reduce the
problem query to this and still get the Assert crash:
select random()
from radicado tablesample bernoulli (9.7)
where radi_texto = radi_inst_actu
limit 33;
None of the elements of this query can be removed without causing the
Assert to go away, which is weird because none of them have any apparent
connection to partition pruning.
With the Assert taken out, we find that this is the plan that's causing
the problem:
QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit (cost=0.00..919.71 rows=33 width=8)
-> Append (cost=0.00..12792.40 rows=459 width=8)
-> Sample Scan on radicado2012 (cost=0.00..2939.18 rows=93 width=8)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Gather (cost=1000.00..2485.72 rows=93 width=8)
Workers Planned: 2
-> Parallel Append (cost=0.00..1476.18 rows=39 width=0)
-> Sample Scan on radicado2013_part00
(cost=0.00..1475.99 rows=47 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Sample Scan on radicado2013_part01
(cost=0.00..1461.88 rows=46 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Gather (cost=1000.00..2491.15 rows=93 width=8)
Workers Planned: 2
-> Parallel Append (cost=0.00..1481.62 rows=39 width=0)
-> Sample Scan on radicado2014_part00
(cost=0.00..1481.42 rows=47 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Sample Scan on radicado2014_part01
(cost=0.00..1464.05 rows=46 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Gather (cost=1000.00..2482.47 rows=93 width=8)
Workers Planned: 2
-> Parallel Append (cost=0.00..1472.93 rows=39 width=0)
-> Sample Scan on radicado2015_part00
(cost=0.00..1472.74 rows=47 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Sample Scan on radicado2015_part01
(cost=0.00..1454.28 rows=46 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Gather (cost=1000.00..2380.72 rows=86 width=8)
Workers Planned: 2
-> Parallel Append (cost=0.00..1371.90 rows=36 width=0)
-> Sample Scan on radicado2016_part00
(cost=0.00..1371.72 rows=43 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Sample Scan on radicado2016_part01
(cost=0.00..1365.21 rows=43 width=0)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
-> Sample Scan on radicado2017 (cost=0.00..10.87 rows=1 width=8)
Sampling: bernoulli ('9.7'::real)
Filter: (radi_texto = radi_inst_actu)
(44 rows)
Now that seems to me to be a rather weird plan: why doesn't it prefer
to flatten everything into one parallel append? Indeed, if you take
out any of the remaining query parts such as the LIMIT, that's what
it does. I think that its willingness to do this is actually kind
of a bug, because this query is going to be a total disaster in terms
of the number of workers it will try to use --- way more than the
user would expect given max_parallel_workers_per_gather = 2.
In any case, I now understand David's concern about creating a
usable regression test case that produces a plan like this.
It seems to depend on a very corner-case-y and possibly buggy
set of costing behaviors.
So, while I'd be willing to go ahead and commit the Assert-removal, this
actually increases my concern about whether a correct set of pruning
instructions get generated in cases like this, because I now feel fairly
confident in saying that we haven't tested the logic for such cases *at
all*. (Note that Jaime's query doesn't have any pruning steps after the
dust settles --- if make_partition_pruneinfo doesn't crash, it eventually
figures out that there are no quals usable for pruning. So the fact that
it goes through without the Assert does nothing to assuage my fear.)
I think we'd be well advised to either change the logic to prohibit
multi-level pruning plans from being generated, or do something to
allow forcing them to be generated for testing purposes.
regards, tom lane
