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.
