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