(Sorry for not continuing the thread in 54418d75.2000...@joh.to , but I don't have the original email anymore.)
So I'm in the same pickle again. According to pg_stat_user_indexes an index is being used all the time. However, it's only being used by mergejoinscansel() to compare these two plans: => explain analyze select * from orders child join orders parent on (parent.orderid = child.parentorderid) where child.orderid = 1161771612; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..15.56 rows=1 width=2910) (actual time=0.401..0.402 rows=1 loops=1) -> Index Scan using orders_pkey on orders child (cost=0.00..7.78 rows=1 width=1455) (actual time=0.367..0.367 rows=1 loops=1) Index Cond: (orderid = 1161771612) -> Index Scan using orders_pkey on orders parent (cost=0.00..7.78 rows=1 width=1455) (actual time=0.027..0.028 rows=1 loops=1) Index Cond: (orderid = child.parentorderid) Total runtime: 0.852 ms (6 rows) => set enable_nestloop to false; set enable_hashjoin to false; SET SET => explain select * from orders child join orders parent on (parent.orderid = child.parentorderid) where child.orderid = 1161771612; QUERY PLAN --------------------------------------------------------------------------------------------------------- Merge Join (cost=1804805.57..97084775.33 rows=1 width=2910) Merge Cond: (parent.orderid = child.parentorderid) -> Index Scan using orders_pkey on orders parent (cost=0.00..96776686.40 rows=123232448 width=1455) -> Sort (cost=7.79..7.79 rows=1 width=1455) Sort Key: child.parentorderid -> Index Scan using orders_pkey on orders child (cost=0.00..7.78 rows=1 width=1455) Index Cond: (orderid = 1161771612) (7 rows) The merge join plan is pretty obviously shit and the fact that the planner got a better estimate for it by peeking through the index had zero effect. I think it would be really important to have a way to turn off get_actual_variable_range() for a specific index during runtime. Would a C level hook be acceptable for this? .m