Thread

1. get_actual_variable_range vs idx_scan/idx_tup_fetch, again

   Marko Tiikkaja <marko@joh.to> — 2018-03-05T14:54:53Z

   (Sorry for not continuing the thread in 54418D75.2000303@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
   

2. Re: get_actual_variable_range vs idx_scan/idx_tup_fetch, again

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-03-05T15:08:57Z

   Marko Tiikkaja <marko@joh.to> 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
   
   
   

3. Re: get_actual_variable_range vs idx_scan/idx_tup_fetch, again

   Marko Tiikkaja <marko@joh.to> — 2018-03-05T15:12:59Z

   On Mon, Mar 5, 2018 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   > Marko Tiikkaja <marko@joh.to> 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?
   >
   
   Yes.  I want to drop it, as I think it's useless, but it's hard to be 100%
   sure.
   
   
   > > 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.
   >
   
   Because I'm certain the planner would make the right choice even without
   the index, and I want it gone.
   
   
   .m