public inbox for [email protected]
help / color / mirror / Atom feedsubquery plan rows = 1, but it's merge joined instead of index lookup
2+ messages / 2 participants
[nested] [flat]
* subquery plan rows = 1, but it's merge joined instead of index lookup
@ 2024-04-11 12:57 ilya Basin <[email protected]>
2024-04-11 14:21 ` Re: subquery plan rows = 1, but it's merge joined instead of index lookup Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: ilya Basin @ 2024-04-11 12:57 UTC (permalink / raw)
To: [email protected]
Hi List.
I have a form with filter inputs and this form produces SQL like:
select * from t where id in( select id from t join filter1... join filter2... )
Usually the query plan has the "in" subquery at the beginning and the result table at the end.
The subquery plan rows is usually 1, actual rows is usually 8. The result table is usually joined using the index scan.
When using more filters than usual at once, the query plan turns upside down and the result table is fully scanned without any filter and before all joins despite its plan rows =200,000 and the subquery plan rows is still =1.
I'm trying to understand why. Is there some complexity limit after which the planner starts acting dumb?
Also, I tried to force the desired plan (only to see if its cost is really higher than the cost of the default plan). However, pg_hint_plan just prints "not used hints" for some of my NestLoop() hints while accepting the others. And set enable_mergejoin = OFF and such only change the join method, but don't fix the order.
explain (FORMAT JSON)
SELECT "bookplanningmain$edition"."id",
"bookplanningmain$edition"."title",
"bookplanningmain$edition"."subtitle",
"bookplanningmain$edition"."editionnumber",
"bookplanningmain$edition"."abouttheauthor",
"bookplanningmain$edition"."plannedinitialmanuscriptdeliverydate",
"bookplanningmain$edition"."actualinitialmanuscriptdeliverydate",
"bookplanningmain$edition"."plannedfinalmanuscriptdeliverydate",
"bookplanningmain$edition"."actualfinalmanuscriptdeliverydate",
"bookplanningmain$edition"."plannedpublicationdate",
"bookplanningmain$edition"."approximatenumberofpages",
"bookplanningmain$edition"."standardcopyrighttexts",
"bookplanningmain$edition"."copyrightstandardtext",
"bookplanningmain$edition"."trademarkqualifiertext",
"bookplanningmain$edition"."productliabilitytext",
"bookplanningmain$edition"."projectstatus_old",
"bookplanningmain$edition"."springerreference",
"bookplanningmain$edition"."additionalbibliographicinformation",
"bookplanningmain$edition"."numberofromanpages",
"bookplanningmain$edition"."numberofarabicpages",
"bookplanningmain$edition"."numberofillustrationscolor",
"bookplanningmain$edition"."numberofillustrationsbw",
"bookplanningmain$edition"."numberofchapters",
"bookplanningmain$edition"."expectedannualsales",
"bookplanningmain$edition"."originaltitle",
"bookplanningmain$edition"."originalpublisher",
"bookplanningmain$edition"."originalcopyrightholder",
"bookplanningmain$edition"."originalpublicationyear",
"bookplanningmain$edition"."openaccess",
"bookplanningmain$edition"."openaccesslicensetext",
"bookplanningmain$edition"."standardoalicensetext",
"bookplanningmain$edition"."publicitytitle",
"bookplanningmain$edition"."copublishinginformation",
"bookplanningmain$edition"."standardcopublishinginformation",
"bookplanningmain$edition"."copublisherisbn",
"bookplanningmain$edition"."copyrightcommentukstandard",
"bookplanningmain$edition"."copyrightcommentpreviousedition",
"bookplanningmain$edition"."copyrightcommentopenaccess",
"bookplanningmain$edition"."copyrightcommentundisclaimer",
"bookplanningmain$edition"."copyrightcommentthirdparty",
"bookplanningmain$edition"."copyrightcommenthybridbook",
"bookplanningmain$edition"."copyrightcommentphdthesis",
"bookplanningmain$edition"."copyrightcommentautomatedtranslation",
"bookplanningmain$edition"."copyrightcommentnonoaigodisclaimer",
"bookplanningmain$edition"."copyrightholder",
"bookplanningmain$edition"."copyrightyear",
"bookplanningmain$edition"."copyrightyearformereditions",
"bookplanningmain$edition"."copyrightyearfreeze",
"bookplanningmain$edition"."copyrightyearfixed",
"bookplanningmain$edition"."isbnassigneddate",
"bookplanningmain$edition"."fullpromotioncheckcompleteddate",
"bookplanningmain$edition"."fullpromotioncheckrejecteddate",
"bookplanningmain$edition"."competingtitles",
"bookplanningmain$edition"."electronicsupplementarymaterial",
"bookplanningmain$edition"."subjectcollectionoverridden",
"bookplanningmain$edition"."isimporttobfluxplanning",
"bookplanningmain$edition"."hybridbook",
"bookplanningmain$edition"."intellectualunitid",
"bookplanningmain$edition"."editionid",
"bookplanningmain$edition"."openaccesschapterdetails",
"bookplanningmain$edition"."keybookforlibraries",
"bookplanningmain$edition"."approximateduration",
"bookplanningmain$edition"."duration",
"bookplanningmain$edition"."plagiarismcheckrequired",
"bookplanningmain$edition"."keytitlecandidate",
"bookplanningmain$edition"."keytitlepitch",
"bookplanningmain$edition"."textbookcourses",
"bookplanningmain$edition"."relatedspringernaturetitles",
"bookplanningmain$edition"."pricingremarkdecision",
"bookplanningmain$edition"."pricelockuntil",
"bookplanningmain$edition"."onlinedate",
"bookplanningmain$edition"."doi",
"bookplanningmain$edition"."accessibility",
"bookplanningmain$edition"."keycorporatetitle",
"bookplanningmain$edition"."bfluxexportstatus",
"bookplanningmain$edition"."freeaccessembargodate",
"bookplanningmain$edition"."freeaccessembargoperiod",
"bookplanningmain$edition"."evaluationhistory",
"bookplanningmain$edition"."commentfromoriginator",
"bookplanningmain$edition"."commenttooriginator",
"bookplanningmain$edition"."productionnotescopyediting",
"bookplanningmain$edition"."productionnotesproofs",
"bookplanningmain$edition"."productionnotescomplimentarycopies",
"bookplanningmain$edition"."productionnotesmiscellaneous",
"bookplanningmain$edition"."productionnotesfrontmatter",
"bookplanningmain$edition"."chapterproofs",
"bookplanningmain$edition"."bookproofs",
"bookplanningmain$edition"."moremediaapp",
"bookplanningmain$edition"."restrictesm",
"bookplanningmain$edition"."bookcoverfiguretext",
"bookplanningmain$edition"."externalcomment",
"bookplanningmain$edition"."displayresponsibleeditorinfrontmatter",
"bookplanningmain$edition"."fundinginformation",
"bookplanningmain$edition"."fundinginformationoverridden",
"bookplanningmain$edition"."externalreviewrequired",
"bookplanningmain$edition"."tprhighrisk",
"bookplanningmain$edition"."revisionrequired",
"bookplanningmain$edition"."rejectionnote",
"bookplanningmain$edition"."numberofredrawnimages",
"bookplanningmain$edition"."prepareforpublicationdate",
"bookplanningmain$edition"."originalpublicationcopyrightnotes",
"bookplanningmain$edition"."typeareaheight",
"bookplanningmain$edition"."typeareawidth",
"bookplanningmain$edition"."colorimagesinprintedbook",
"bookplanningmain$edition"."productionnotesindex",
"bookplanningmain$edition"."springerprotocols",
"bookplanningmain$edition"."productionnotesads",
"bookplanningmain$edition"."standardcopyrightholder",
"bookplanningmain$edition"."eauthoringneeded",
"dj3BookPlanningUtils$AuditedSNEntity"."createddate",
"dj3BookPlanningUtils$AuditedSNEntity"."changeddate",
"dj3BookPlanningUtils$AuditedSNEntity"."system$owner",
"dj3BookPlanningUtils$AuditedSNEntity"."system$changedby",
CASE
WHEN NOT "bookplanningmain$edition"."springerreference" = TRUE
AND "bookplanningmain$edition"."actualfinalmanuscriptdeliverydate" IS NULL THEN TRUE
ELSE FALSE
END AS "__sec_1",
CASE
WHEN "bookplanningmain$edition"."actualfinalmanuscriptdeliverydate" IS NULL THEN TRUE
ELSE FALSE
END AS "__sec_2"
FROM "bookplanningmain$edition"
INNER JOIN "bookplanningutils$auditedsnentity" "dj3BookPlanningUtils$AuditedSNEntity" ON "dj3BookPlanningUtils$AuditedSNEntity"."id" = "bookplanningmain$edition"."id"
WHERE "bookplanningmain$edition"."id" IN
(SELECT "x1BookPlanningMain$Edition"."id"
FROM "bookplanningmain$edition" "x1BookPlanningMain$Edition"
INNER JOIN "bookplanningmain$edition_publishingsegmentlocal" "atj7BookPlanningMain$Edition_PublishingSegmentLocal" ON "atj7BookPlanningMain$Edition_PublishingSegmentLocal"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "rgstructure$publishingsegmentlocal" "RGStructure.PublishingSegmentLocal" ON "RGStructure.PublishingSegmentLocal"."id" = "atj7BookPlanningMain$Edition_PublishingSegmentLocal"."rgstructure$publishingsegmentlocalid"
INNER JOIN "bookplanningmain$rendition_edition" "atj8BookPlanningMain$Rendition_Edition" ON "atj8BookPlanningMain$Rendition_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$rendition" "BookPlanningMain.Rendition" ON "BookPlanningMain.Rendition"."id" = "atj8BookPlanningMain$Rendition_Edition"."bookplanningmain$renditionid"
INNER JOIN "bookplanningmain$event_edition" "atj9BookPlanningMain$Event_Edition" ON "atj9BookPlanningMain$Event_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$event" "BookPlanningMain.Event" ON "BookPlanningMain.Event"."id" = "atj9BookPlanningMain$Event_Edition"."bookplanningmain$eventid"
INNER JOIN "bookplanningmain$edition_productionservice" "atj10BookPlanningMain$Edition_ProductionService" ON "atj10BookPlanningMain$Edition_ProductionService"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
-- uncommend me:
-- INNER JOIN "bookplanningmain$edition_entrypoint" "atj11BookPlanningMain$Edition_EntryPoint" ON "atj11BookPlanningMain$Edition_EntryPoint"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$event_eventstatus" "atj12BookPlanningMain$Event_EventStatus" ON "atj12BookPlanningMain$Event_EventStatus"."bookplanningmain$eventid" = "BookPlanningMain.Event"."id"
WHERE ("RGStructure.PublishingSegmentLocal"."_lowerbepmresponsiblemanagerl1accountname" = 'kosh01'
OR "RGStructure.PublishingSegmentLocal"."_lowerbepmresponsiblemanagerl2accountname" = 'kosh01')
AND "RGStructure.PublishingSegmentLocal"."id" = 168884986026396238
AND "BookPlanningMain.Rendition"."isprimary" = TRUE
AND ("BookPlanningMain.Rendition"."id" IN
(SELECT "atj1BookPlanningMain$Rendition_DeliveryStatus"."bookplanningmain$renditionid"
FROM "bookplanningmain$rendition_deliverystatus" "atj1BookPlanningMain$Rendition_DeliveryStatus"
WHERE "atj1BookPlanningMain$Rendition_DeliveryStatus"."bookplanningmain$renditionid" = "BookPlanningMain.Rendition"."id"
AND "atj1BookPlanningMain$Rendition_DeliveryStatus"."avatarclient$va_delivery_statusid" = 65865144550293507 )
)
AND NOT EXISTS
(SELECT "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookplanningmain$renditionid"
FROM "bookseries$bibliographicseriesrelation_rendition" "atj3BookSeries$BibliographicSeriesRelation_Rendition"
INNER JOIN "bookseries$bibliographicseriesrelation_bibliographicseries" "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries" ON "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries"."bookseries$bibliographicseriesrelationid" = "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookseries$bibliographicseriesrelationid"
WHERE "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookplanningmain$renditionid" = "BookPlanningMain.Rendition"."id"
AND "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries"."bookseries$bibliographicseriesid" = 237564880357427438 )
AND NOT EXISTS
(SELECT "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$editionid"
FROM "bookplanningmain$staffuser_edition" "atj5BookPlanningMain$StaffUser_Edition"
INNER JOIN "bookplanningmain$staffuser_stafftype" "atj6BookPlanningMain$StaffUser_StaffType" ON "atj6BookPlanningMain$StaffUser_StaffType"."bookplanningmain$staffuserid" = "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$staffuserid"
WHERE "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
AND "atj6BookPlanningMain$StaffUser_StaffType"."avatarclient$va_col_inhouse_staffid" = 92605267337805843 )
AND "x1BookPlanningMain$Edition"."springerreference" = false
AND "atj10BookPlanningMain$Edition_ProductionService"."avatarclient$va_production_serviceid" = 96827391988465668
AND "atj12BookPlanningMain$Event_EventStatus"."avatarclient$va_event_statusid" = 119626865102028810
AND "BookPlanningMain.Event"."eventdate" >= '2024-03-14 00:00:00'
AND "BookPlanningMain.Event"."eventdate" < '2024-03-15 00:00:00' )
ORDER BY "bookplanningmain$edition"."id" ASC
offset 100 LIMIT 20
Attachments:
[text/plain] 2024-04-11-2.sql (11.1K, 2-2024-04-11-2.sql)
download | inline:
explain (FORMAT JSON)
SELECT "bookplanningmain$edition"."id",
"bookplanningmain$edition"."title",
"bookplanningmain$edition"."subtitle",
"bookplanningmain$edition"."editionnumber",
"bookplanningmain$edition"."abouttheauthor",
"bookplanningmain$edition"."plannedinitialmanuscriptdeliverydate",
"bookplanningmain$edition"."actualinitialmanuscriptdeliverydate",
"bookplanningmain$edition"."plannedfinalmanuscriptdeliverydate",
"bookplanningmain$edition"."actualfinalmanuscriptdeliverydate",
"bookplanningmain$edition"."plannedpublicationdate",
"bookplanningmain$edition"."approximatenumberofpages",
"bookplanningmain$edition"."standardcopyrighttexts",
"bookplanningmain$edition"."copyrightstandardtext",
"bookplanningmain$edition"."trademarkqualifiertext",
"bookplanningmain$edition"."productliabilitytext",
"bookplanningmain$edition"."projectstatus_old",
"bookplanningmain$edition"."springerreference",
"bookplanningmain$edition"."additionalbibliographicinformation",
"bookplanningmain$edition"."numberofromanpages",
"bookplanningmain$edition"."numberofarabicpages",
"bookplanningmain$edition"."numberofillustrationscolor",
"bookplanningmain$edition"."numberofillustrationsbw",
"bookplanningmain$edition"."numberofchapters",
"bookplanningmain$edition"."expectedannualsales",
"bookplanningmain$edition"."originaltitle",
"bookplanningmain$edition"."originalpublisher",
"bookplanningmain$edition"."originalcopyrightholder",
"bookplanningmain$edition"."originalpublicationyear",
"bookplanningmain$edition"."openaccess",
"bookplanningmain$edition"."openaccesslicensetext",
"bookplanningmain$edition"."standardoalicensetext",
"bookplanningmain$edition"."publicitytitle",
"bookplanningmain$edition"."copublishinginformation",
"bookplanningmain$edition"."standardcopublishinginformation",
"bookplanningmain$edition"."copublisherisbn",
"bookplanningmain$edition"."copyrightcommentukstandard",
"bookplanningmain$edition"."copyrightcommentpreviousedition",
"bookplanningmain$edition"."copyrightcommentopenaccess",
"bookplanningmain$edition"."copyrightcommentundisclaimer",
"bookplanningmain$edition"."copyrightcommentthirdparty",
"bookplanningmain$edition"."copyrightcommenthybridbook",
"bookplanningmain$edition"."copyrightcommentphdthesis",
"bookplanningmain$edition"."copyrightcommentautomatedtranslation",
"bookplanningmain$edition"."copyrightcommentnonoaigodisclaimer",
"bookplanningmain$edition"."copyrightholder",
"bookplanningmain$edition"."copyrightyear",
"bookplanningmain$edition"."copyrightyearformereditions",
"bookplanningmain$edition"."copyrightyearfreeze",
"bookplanningmain$edition"."copyrightyearfixed",
"bookplanningmain$edition"."isbnassigneddate",
"bookplanningmain$edition"."fullpromotioncheckcompleteddate",
"bookplanningmain$edition"."fullpromotioncheckrejecteddate",
"bookplanningmain$edition"."competingtitles",
"bookplanningmain$edition"."electronicsupplementarymaterial",
"bookplanningmain$edition"."subjectcollectionoverridden",
"bookplanningmain$edition"."isimporttobfluxplanning",
"bookplanningmain$edition"."hybridbook",
"bookplanningmain$edition"."intellectualunitid",
"bookplanningmain$edition"."editionid",
"bookplanningmain$edition"."openaccesschapterdetails",
"bookplanningmain$edition"."keybookforlibraries",
"bookplanningmain$edition"."approximateduration",
"bookplanningmain$edition"."duration",
"bookplanningmain$edition"."plagiarismcheckrequired",
"bookplanningmain$edition"."keytitlecandidate",
"bookplanningmain$edition"."keytitlepitch",
"bookplanningmain$edition"."textbookcourses",
"bookplanningmain$edition"."relatedspringernaturetitles",
"bookplanningmain$edition"."pricingremarkdecision",
"bookplanningmain$edition"."pricelockuntil",
"bookplanningmain$edition"."onlinedate",
"bookplanningmain$edition"."doi",
"bookplanningmain$edition"."accessibility",
"bookplanningmain$edition"."keycorporatetitle",
"bookplanningmain$edition"."bfluxexportstatus",
"bookplanningmain$edition"."freeaccessembargodate",
"bookplanningmain$edition"."freeaccessembargoperiod",
"bookplanningmain$edition"."evaluationhistory",
"bookplanningmain$edition"."commentfromoriginator",
"bookplanningmain$edition"."commenttooriginator",
"bookplanningmain$edition"."productionnotescopyediting",
"bookplanningmain$edition"."productionnotesproofs",
"bookplanningmain$edition"."productionnotescomplimentarycopies",
"bookplanningmain$edition"."productionnotesmiscellaneous",
"bookplanningmain$edition"."productionnotesfrontmatter",
"bookplanningmain$edition"."chapterproofs",
"bookplanningmain$edition"."bookproofs",
"bookplanningmain$edition"."moremediaapp",
"bookplanningmain$edition"."restrictesm",
"bookplanningmain$edition"."bookcoverfiguretext",
"bookplanningmain$edition"."externalcomment",
"bookplanningmain$edition"."displayresponsibleeditorinfrontmatter",
"bookplanningmain$edition"."fundinginformation",
"bookplanningmain$edition"."fundinginformationoverridden",
"bookplanningmain$edition"."externalreviewrequired",
"bookplanningmain$edition"."tprhighrisk",
"bookplanningmain$edition"."revisionrequired",
"bookplanningmain$edition"."rejectionnote",
"bookplanningmain$edition"."numberofredrawnimages",
"bookplanningmain$edition"."prepareforpublicationdate",
"bookplanningmain$edition"."originalpublicationcopyrightnotes",
"bookplanningmain$edition"."typeareaheight",
"bookplanningmain$edition"."typeareawidth",
"bookplanningmain$edition"."colorimagesinprintedbook",
"bookplanningmain$edition"."productionnotesindex",
"bookplanningmain$edition"."springerprotocols",
"bookplanningmain$edition"."productionnotesads",
"bookplanningmain$edition"."standardcopyrightholder",
"bookplanningmain$edition"."eauthoringneeded",
"dj3BookPlanningUtils$AuditedSNEntity"."createddate",
"dj3BookPlanningUtils$AuditedSNEntity"."changeddate",
"dj3BookPlanningUtils$AuditedSNEntity"."system$owner",
"dj3BookPlanningUtils$AuditedSNEntity"."system$changedby",
CASE
WHEN NOT "bookplanningmain$edition"."springerreference" = TRUE
AND "bookplanningmain$edition"."actualfinalmanuscriptdeliverydate" IS NULL THEN TRUE
ELSE FALSE
END AS "__sec_1",
CASE
WHEN "bookplanningmain$edition"."actualfinalmanuscriptdeliverydate" IS NULL THEN TRUE
ELSE FALSE
END AS "__sec_2"
FROM "bookplanningmain$edition"
INNER JOIN "bookplanningutils$auditedsnentity" "dj3BookPlanningUtils$AuditedSNEntity" ON "dj3BookPlanningUtils$AuditedSNEntity"."id" = "bookplanningmain$edition"."id"
WHERE "bookplanningmain$edition"."id" IN
(SELECT "x1BookPlanningMain$Edition"."id"
FROM "bookplanningmain$edition" "x1BookPlanningMain$Edition"
INNER JOIN "bookplanningmain$edition_publishingsegmentlocal" "atj7BookPlanningMain$Edition_PublishingSegmentLocal" ON "atj7BookPlanningMain$Edition_PublishingSegmentLocal"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "rgstructure$publishingsegmentlocal" "RGStructure.PublishingSegmentLocal" ON "RGStructure.PublishingSegmentLocal"."id" = "atj7BookPlanningMain$Edition_PublishingSegmentLocal"."rgstructure$publishingsegmentlocalid"
INNER JOIN "bookplanningmain$rendition_edition" "atj8BookPlanningMain$Rendition_Edition" ON "atj8BookPlanningMain$Rendition_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$rendition" "BookPlanningMain.Rendition" ON "BookPlanningMain.Rendition"."id" = "atj8BookPlanningMain$Rendition_Edition"."bookplanningmain$renditionid"
INNER JOIN "bookplanningmain$event_edition" "atj9BookPlanningMain$Event_Edition" ON "atj9BookPlanningMain$Event_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$event" "BookPlanningMain.Event" ON "BookPlanningMain.Event"."id" = "atj9BookPlanningMain$Event_Edition"."bookplanningmain$eventid"
INNER JOIN "bookplanningmain$edition_productionservice" "atj10BookPlanningMain$Edition_ProductionService" ON "atj10BookPlanningMain$Edition_ProductionService"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
-- uncommend me:
-- INNER JOIN "bookplanningmain$edition_entrypoint" "atj11BookPlanningMain$Edition_EntryPoint" ON "atj11BookPlanningMain$Edition_EntryPoint"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
INNER JOIN "bookplanningmain$event_eventstatus" "atj12BookPlanningMain$Event_EventStatus" ON "atj12BookPlanningMain$Event_EventStatus"."bookplanningmain$eventid" = "BookPlanningMain.Event"."id"
WHERE ("RGStructure.PublishingSegmentLocal"."_lowerbepmresponsiblemanagerl1accountname" = 'kosh01'
OR "RGStructure.PublishingSegmentLocal"."_lowerbepmresponsiblemanagerl2accountname" = 'kosh01')
AND "RGStructure.PublishingSegmentLocal"."id" = 168884986026396238
AND "BookPlanningMain.Rendition"."isprimary" = TRUE
AND ("BookPlanningMain.Rendition"."id" IN
(SELECT "atj1BookPlanningMain$Rendition_DeliveryStatus"."bookplanningmain$renditionid"
FROM "bookplanningmain$rendition_deliverystatus" "atj1BookPlanningMain$Rendition_DeliveryStatus"
WHERE "atj1BookPlanningMain$Rendition_DeliveryStatus"."bookplanningmain$renditionid" = "BookPlanningMain.Rendition"."id"
AND "atj1BookPlanningMain$Rendition_DeliveryStatus"."avatarclient$va_delivery_statusid" = 65865144550293507 )
)
AND NOT EXISTS
(SELECT "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookplanningmain$renditionid"
FROM "bookseries$bibliographicseriesrelation_rendition" "atj3BookSeries$BibliographicSeriesRelation_Rendition"
INNER JOIN "bookseries$bibliographicseriesrelation_bibliographicseries" "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries" ON "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries"."bookseries$bibliographicseriesrelationid" = "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookseries$bibliographicseriesrelationid"
WHERE "atj3BookSeries$BibliographicSeriesRelation_Rendition"."bookplanningmain$renditionid" = "BookPlanningMain.Rendition"."id"
AND "atj4BookSeries$BibliographicSeriesRelation_BibliographicSeries"."bookseries$bibliographicseriesid" = 237564880357427438 )
AND NOT EXISTS
(SELECT "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$editionid"
FROM "bookplanningmain$staffuser_edition" "atj5BookPlanningMain$StaffUser_Edition"
INNER JOIN "bookplanningmain$staffuser_stafftype" "atj6BookPlanningMain$StaffUser_StaffType" ON "atj6BookPlanningMain$StaffUser_StaffType"."bookplanningmain$staffuserid" = "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$staffuserid"
WHERE "atj5BookPlanningMain$StaffUser_Edition"."bookplanningmain$editionid" = "x1BookPlanningMain$Edition"."id"
AND "atj6BookPlanningMain$StaffUser_StaffType"."avatarclient$va_col_inhouse_staffid" = 92605267337805843 )
AND "x1BookPlanningMain$Edition"."springerreference" = false
AND "atj10BookPlanningMain$Edition_ProductionService"."avatarclient$va_production_serviceid" = 96827391988465668
AND "atj12BookPlanningMain$Event_EventStatus"."avatarclient$va_event_statusid" = 119626865102028810
AND "BookPlanningMain.Event"."eventdate" >= '2024-03-14 00:00:00'
AND "BookPlanningMain.Event"."eventdate" < '2024-03-15 00:00:00' )
ORDER BY "bookplanningmain$edition"."id" ASC
offset 100 LIMIT 20
[application/json] bad.json (75.7K, 3-bad.json)
download
[image/svg+xml] bad.svg (161.0K, 4-bad.svg)
download | view image
[application/json] good.json (77.4K, 5-good.json)
download
[image/svg+xml] good.svg (154.9K, 6-good.svg)
download | view image
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: subquery plan rows = 1, but it's merge joined instead of index lookup
2024-04-11 12:57 subquery plan rows = 1, but it's merge joined instead of index lookup ilya Basin <[email protected]>
@ 2024-04-11 14:21 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-04-11 14:21 UTC (permalink / raw)
To: ilya Basin <[email protected]>; [email protected]
On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote:
> Is there some complexity limit after which the planner starts acting dumb?
Yes, "join_collapse_limit" and "from_collapse_limit".
You can try increasing them.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-11 14:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-11 12:57 subquery plan rows = 1, but it's merge joined instead of index lookup ilya Basin <[email protected]>
2024-04-11 14:21 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox