Home > mailing lists

Thread: Delete in list with join using partitions generates monstrous query plan

From

Alex

Date:

18 December 2020, 16:22:53

I have a table with 80 partitions (product_property) partitioned on some column not used in the following query:

DELETE

FROM "product_property"

WHERE "product_property".."id" IN

( SELECT "product_property"."id"

FROM "product_property"

JOIN "product" ON "product_property"."product" = "product"."id"

WHERE ("product"."language" = cast('ro' AS regconfig)

AND "product_property"."secondary_meaning" = 'V') )

This query creates a plan where each partition uses a hash semi join with 80 index scans (one for each partition), with a total of 6643 index scans.

Interesting is that 5443 out of 6643 index scans are „never executed” .

This query also uses lots of memory (>500MB) and the planner time is ~10sec.

Querying only the subquery, returns no rows and plan uses one index scan per each partition, as expected, with a planning time of 118ms.

As the subquery plan is simple enough, I expected the delete to use something similar.

Any suggestion on improving the planned execution? Or it’s something you guys can do to optimize the planner on dev side?

I’ve attached the explain analyze for whole query and subquery.

Thanks