Re: get_actual_variable_range vs idx_scan/idx_tup_fetch, again

[Tom Lane]

Marko Tiikkaja  writes:
> 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:

If it's not being used otherwise, could you drop it?

> 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?

You haven't really made a case for why you (or anyone else) should care.
As long as the planner makes the right choice, having investigated a wrong
choice doesn't seem like a bug to me.

regards, tom lane

get_actual_variable_range vs idx_scan/idx_tup_fetch, again

[Marko Tiikkaja]

(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