pg_hint_plan icon indicating copy to clipboard operation
pg_hint_plan copied to clipboard

Can't force postgre to use nested loop on index

Open OverDrone opened this issue 3 years ago • 1 comments

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:

  1. scan t1, filter flag=false, materialize (10 mil records instead of 1 fetched by index)
  2. nested loop t2 with the result of previous step

I expect:

  1. nested loop t1-t2 by parent_id=3 (1 record in t1, 1 record in t2)
  2. lookup t2 fields by parent_id index
  3. 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)

OverDrone avatar Feb 11 '22 18:02 OverDrone