pg_hint_plan
pg_hint_plan copied to clipboard
Can't force postgre to use nested loop on index
Hello! I'm trying to force specific execution plan (very simple really), but postgre+pg_hint_plan v13 still using shitty plans. Can you suggest any hint combination to force nested loop join exactly by "on" condition without any prefiltering/materialization?
Table1: id UUID primary key, parent_id UUID, flag BOOLEAN. parent_id indexed.
Table2: id UUID primary key
Table1 contains:
id=1,parent_id=null,flag=true - 1 row
id=2,parent_id=null,flag=false - 1 row
id=...,parent_id=1,flag=true - 10 mil rows
id=...,parent_id=2,flag=false - 10 mil rows
Table2 contains:
id=3 (present in t1)
/*+
Leading((t2 t1))
NestLoop(t1 t2)
*/
select ...
from table2 t2
inner join table1 t1
on t1.parent_id = t2.id
and not t1.flag
Postgre plan is:
- scan t1, filter flag=false, materialize (10 mil records instead of 1 fetched by index)
- nested loop t2 with the result of previous step
I expect:
- nested loop t1-t2 by parent_id=3 (1 record in t1, 1 record in t2)
- lookup t2 fields by parent_id index
- filter t1.flag=false
If I add IndexScan(t1 parent_id_index), then postgre just materialize not the table but the index instead, but still 10mil records. If I remove "NOT flag" condition, all seems working. For some reason postgre thinks that filtering 20mil records to 10 mil records then materializing 10mil records in a temp table is more preferrable than using parent_id index which may return 10mil matches for 2 rows out of 20mil (imagine a probability)