Re: [HACKERS] Runtime Partition Pruning

On Thu, Dec 21, 2017 at 4:01 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> The problem is down to the logic in choose_custom_plan() only choosing
> a generic plan if the average cost of the generic plan is less than
> the average custom plan cost. The problem is that the generic plan can
> have many extra Append subnodes in comparison to the custom plan, all
> of which are taken into account in the total plan cost, but these may
> be pruned during execution. The logic in choose_custom_plan() has no
> idea about this. I don't have any bright ideas on how to fix this
> yet, as, suppose a PREPAREd statement like the following comes along:
>
> PREPARE q3 (int, int) AS SELECT * FROM partitioned_table WHERE partkey
> BETWEEN $1 AND $2;
>
> the run-time pruning may prune it down no subplans, all subplans, or
> any number in between. So we can't do anything like take the total
> Append cost to be the highest costing of its subplans, and likely
> using the average cost might not be a good idea either.

Well, I do think we need to make some kind of estimate. It may be a
bad estimate, but if we do nothing, we're estimating that no pruning
at all will happen, which is probably not right either. I mean, if we
have...

PREPARE q3 (int, int) AS SELECT * FROM unpartitioned_table WHERE
partkey BETWEEN $1 AND $2;

...that has to decide whether to use an index. And to do that it has
to estimate what fraction of the table will match the BETWEEN clause.
That may be an uninformed guess, but it guesses something. We
probably want to do something here that makes the guess for a
partitioned_table similar to the guess for an unpartitioned_table.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company