subquery plan rows = 1, but it's merge joined instead of index lookup

Started by Ilya Basinabout 2 years ago3 messagesgeneral
Jump to latest
#1Ilya Basin
basinilya@gmail.com

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.

Attachments:

2024-04-11-2.sqltext/plain; charset=UTF-8; name=2024-04-11-2.sqlDownload
bad.jsonapplication/json; name=bad.jsonDownload
bad.svgimage/svg+xml; name=bad.svgDownload
good.jsonapplication/json; name=good.jsonDownload
good.svgimage/svg+xml; name=good.svgDownload
#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Ilya Basin (#1)
Re: subquery plan rows = 1, but it's merge joined instead of index lookup

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

#3Ilya Basin
basinilya@gmail.com
In reply to: Laurenz Albe (#2)
Re: subquery plan rows = 1, but it's merge joined instead of index lookup

Yes! "set join_collapse_limit = 9" was enough to fix the plan, thanks Laurenz.
I will set both to 11.

-------- Original Message --------
From: Laurenz Albe [mailto:laurenz.albe@cybertec.at]
Sent: Thursday, April 11, 2024 at 14:21 UTC
To: ilya Basin; pgsql-general@lists.postgresql.org
Subject: subquery plan rows = 1, but it's merge joined instead of index lookup

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