Support functions for range types

Поиск
Список
Период
Сортировка
От Kim Johan Andersson
Тема Support functions for range types
Дата
Msg-id 222c75fd-43b8-db3e-74a6-bb4fe22f76db@kimmet.dk
обсуждение исходный текст
Ответы Re: Support functions for range types
Список pgsql-general
I was surprised by the poor performance when I first tried to use range 
types. What I expected was that the following two queries would be 
equivalent (see attached script):

postgres=# EXPLAIN ANALYZE SELECT some_number FROM integer_test WHERE 
some_number BETWEEN -2 AND 2;
 
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
  Index Only Scan using integer_test_some_number_idx on integer_test 
(cost=0.28..8.38 rows=5 width=4) (actual time=0.045..0.052 rows=5 loops=1)
    Index Cond: ((some_number >= '-2'::integer) AND (some_number <= 2))
    Heap Fetches: 5
  Planning Time: 0.319 ms
  Execution Time: 0.094 ms
(5 rows)

postgres=# EXPLAIN ANALYZE SELECT some_number FROM integer_test WHERE 
some_number <@ int4range(-2, 2, '[]');
                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------
  Seq Scan on integer_test  (cost=0.00..34.01 rows=10 width=4) (actual 
time=0.585..1.136 rows=5 loops=1)
    Filter: (some_number <@ '[-2,3)'::int4range)
    Rows Removed by Filter: 1996
  Planning Time: 0.175 ms
  Execution Time: 1.164 ms
(5 rows)

But clearly, the planner is not able to use the btree index in the 
presence of the range operator.
So I attempted to add support functions for the 
'elem_contained_by_range' and 'range_contains_elem' operators (patch 
attached):
That gives the following execution plan (applied on 
26f7802beb2a4aafa0903f5bedeb7f1fa6f4f358):

 
QUERY PLAN 


-------------------------------------------------------------------------------------------------------------------------------------------------
  Index Only Scan using integer_test_some_number_idx on integer_test 
(cost=0.28..8.38 rows=10 width=4) (actual time=0.046..0.058 rows=5 loops=1)
    Index Cond: ((some_number >= '-2'::integer) AND (some_number < 3))
    Heap Fetches: 5
  Planning Time: 0.694 ms
  Execution Time: 0.114 ms
(5 rows)

That was what I was hoping to see (even though the row estimate is still 
a bit off).
Unfortunately this only works for the trivial case where the range is 
actually a constant.
The third query in the attached script (range_test.sql) produces the 
following plan, where the support function is not useful:

                                                        QUERY PLAN 


-------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.14..419.56 rows=22 width=12) (actual 
time=3.791..36.549 rows=121 loops=1)
    Join Filter: (integer_test.some_number <@ 
int4range(number_q.one_number, number_q.another_number, '[]'::text))
    Rows Removed by Join Filter: 21890
    CTE number_q
      ->  Function Scan on generate_series  (cost=0.00..0.14 rows=11 
width=8) (actual time=0.063..0.076 rows=11 loops=1)
    ->  CTE Scan on number_q  (cost=0.00..0.22 rows=11 width=8) (actual 
time=0.071..0.107 rows=11 loops=1)
    ->  Materialize  (cost=0.00..39.02 rows=2001 width=4) (actual 
time=0.011..0.516 rows=2001 loops=11)
          ->  Seq Scan on integer_test  (cost=0.00..29.01 rows=2001 
width=4) (actual time=0.077..1.043 rows=2001 loops=1)
  Planning Time: 3.172 ms
  Execution Time: 36.908 ms
(10 rows)

So my question here is, how to go about handling the more interesting 
cases, where we are passed a FuncExpr (instead of a Const)?
Is it even possible to return something useful in this case?

As far as I can tell, the support function is being passed a reference 
to the range constructor function when the range is not a constant.
But I don't have the insight required to build opclauses that can handle 
non-constants.
Any thoughs or pointers on solving this?

    Thanks,
            Kim Johan Andersson
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Federico
Дата:
Сообщение: Re: Order by in a sub query when aggregating the main query
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Support functions for range types