From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | get_actual_variable_range vs idx_scan/idx_tup_fetch, again |
Date: | 2018-03-05 14:54:53 |
Message-ID: | CAL9smLAfhOEQZiuxr6Aw3Rm9yP0+ffMb7-h1B9CX=QFcNbUwSQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
(Sorry for not continuing the thread in 54418D75(dot)2000303(at)joh(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-03-05 15:00:41 | Re: [HACKERS] Lazy hash table for XidInMVCCSnapshot (helps Zipfian a bit) |
Previous Message | David Steele | 2018-03-05 14:48:17 | Re: 2018-03 CFM |