Re: Bad Query Plans on 10.3 vs 9.6

>
> Is the 10.3 plan parallelized at all? It's possible that the planner
> thinks a parallel seqscan is faster than a nonparallel indexscan
> (AFAIR, v10 doesn't have parallel indexscan).
>

Its not because its a delete operation, which doesn't support parallel
anything. Interestingly, the plain select version of the same tries to do
a parallel sequential scan on household instead of using the index.

Also, If I change the query to be just regular INNER JOIN with "match" as
the driving table, it still does a parallel sequential scan. However, if I
change the INNER JOIN to have "household" as the driving table then it will
do an index only scan of household. That unfortunately does not help me
because I am doing a NOT EXISTS clause and I cannot think of a way to
change the query to support that.

>
> The other likely explanation is simply that indexscanning a partitioned
> table is not considered, or not correctly costed. I'm not very sure what
> the state of that code is, but certainly all the v10 partitioning logic is
> still pretty wet behind the ears.
>
> regards, tom lane
>

Could be the partitioning code path, however, I am actually querying the
child partition tables directly (which have no further child partitions),
so I'd assume it would be the normal code flow. To test, I created another
table from the results of the select of the child partitioned table and the
query plan looks exactly the same.