From: | Alex <cdalxndr(at)yahoo(dot)com> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Delete in list with join using partitions generates monstrous query plan |
Date: | 2020-12-18 13:22:53 |
Message-ID: | 1966497320.2175534.1608297773488@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I have a table with 80 partitions (product_property) partitioned on somecolumn 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 eachpartition 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 6643index scans are „never executed” .
This query also uses lots of memory(>500MB) and the planner time is ~10sec.
Querying only the subquery, returns norows and plan uses one index scan per each partition, as expected, with aplanning time of 118ms.
As the subquery plan is simple enough, Iexpected the delete to use something similar.
Any suggestion on improving the plannedexecution? Or it’s something you guys can do to optimize the planner on devside?
I’ve attached the explain analyze forwhole query and subquery.
Thanks
Attachment | Content-Type | Size |
---|---|---|
explain_analyze_query.txt | text/plain | 2.0 MB |
explain_analyze_subquery.txt | text/plain | 22.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Colin 't Hart | 2020-12-18 20:18:01 | Re: BUG #16779: psql -e not showing queries |
Previous Message | PG Bug reporting form | 2020-12-18 10:00:00 | BUG #16780: Inconsistent recovery_target_xid handling across platforms |