Support functions for range types
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
Kim Johan Andersson <kimjand@kimmet.dk> writes:
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?
Doesn't look like it to me. You could check whether the RHS is a
range constructor function call, but there's a big semantic problem:
int4_range(NULL, ...) converts to a range with an infinite bound,
not a null bound. So translating that to "indxvar >= NULL" would
give the wrong answers. And generally speaking, if the argument
isn't a constant then you're not going to be able to be sure that
it doesn't produce NULL.
I guess you could produce something like
indxvar >= coalesce(argument, minimum-value-of-type)
in cases where the data type has an identifiable minimum resp.
maximum value, but that'd make the whole affair annoyingly
data-type-specific. Not sure it's worth going there.
regards, tom lane
On 25-09-2022 16:43, Tom Lane wrote:
Doesn't look like it to me. You could check whether the RHS is a
range constructor function call, but there's a big semantic problem:
int4_range(NULL, ...) converts to a range with an infinite bound,
not a null bound. So translating that to "indxvar >= NULL" would
give the wrong answers. And generally speaking, if the argument
isn't a constant then you're not going to be able to be sure that
it doesn't produce NULL.
I was suspecting that it would not be possible. So we can peek at the
constructor parameters, but we won't know the actual value until execution.
I guess you could produce something like
indxvar >= coalesce(argument, minimum-value-of-type)
in cases where the data type has an identifiable minimum resp.
maximum value, but that'd make the whole affair annoyingly
data-type-specific. Not sure it's worth going there.
I was hoping it was possible to rely on the existing range mechanics for
handling the data types. Along the lines of adding nodes dependent on
the result of the constructor call.
But if there is no opportunity to make a dynamic expression suitable for
the index, then I guess it won't be possible to make a really useful
support function for range types.
Thanks for the input.
Regards,
Kim Johan Andersson
On Mon, 2022-09-26 at 06:57 +0200, Kim Johan Andersson wrote:
But if there is no opportunity to make a dynamic expression suitable for
the index, then I guess it won't be possible to make a really useful
support function for range types.
I think it could still be useful if it only deals with constant operands.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com