如何优化postgresql查询

Question

这个查询需要13秒才能执行，但是如果我删除了gln不是空条件，那么执行该查询需要2秒时间，为什么以及如何优化查询？

解释：。

https://explain.depesz.com/s/cn8H (带gln支票)

与

https://explain.depesz.com/s/OJap (不含gln )

select "nomenclatureKindGuid1C"
from "mds_nomenclature"
where exists(select *
             from "mds_nomenclature_contractor"
             where "mds_nomenclature"."guid1C" = "mds_nomenclature_contractor"."nomenclatureGuid1C"
               and exists(select *
                          from "mds_contractor"
                          where "mds_nomenclature_contractor"."contractorGuid1C" =
                                "mds_contractor"."guid1C"
                            and "gln" is not null)
               and exists(select *
                          from "mds_price"
                          where "mds_nomenclature_contractor"."guid1C" =
                                "mds_price"."nomenclatureContractorGuid1C"
                            and (("finalVersion" = true and
                                  ("contractorGuid1C" is null or
                                   ("contractorGuid1C" = '0004983d-1d4c-11e9-80c9-0050568b6b27')) and
                                  ("objectGuid1C" is null or
                                   ("objectGuid1C" = 'ae4e3474-7c0d-4ae4-80cb-524ab67b001f')) and
                                  ("purchasePlanDocumentGuid1C" is null or exists(select *
                                                                                  from "mds_purchase_plan_document"
                                                                                  where "mds_price"."purchasePlanDocumentGuid1C" =
                                                                                        "mds_purchase_plan_document"."guid1C"
                                                                                    and "startDate" <= '2020-02-10 18:25:04'
                                                                                    and "endDate" >= '2020-02-10 18:25:04'))))
                            and "mds_nomenclature_contractor"."deleted" is null)
          )

Answer 1

在其中一些情况下，我采取的第一步是使用我需要比较的元组创建一个临时表(因为这似乎是一个复杂的比较)，然后构建整个语句。