Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Alexander Korotkov aekorot...@gmail.com writes: On Mon, Jan 30, 2012 at 1:39 AM, Jeff Davis pg...@j-davis.com wrote: Marking ready for committer, but please apply my comment fixes at your discretion. Patch with your comment fixes is attached. Applied with revisions, some cosmetic, some not so much. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Tue, 2012-01-24 at 16:07 +0400, Alexander Korotkov wrote: Hi! New version of patch is attached. Thank you for the updates. I have a small patch attached. The only code change I made was very minor: I changed the constants used in the penalty function because your version used INFINITE_BOUND_PENALTY when adding an empty range, and that didn't quite make sense to me. If I'm mistaken you can leave it as-is. I also attached range-gist-test.sql, which I used for a performance test. I mix various types of ranges together in a larger table of 1.1M tuples. And then I create a smaller table that only contains normal ranges and empty ranges. There are two tests: 1. Create an index on the big table 2. Do a range join (using overlaps rather than equals) where the smaller table is on the outer side of a nested loop join and an index scan over the larger table on the inner. The index creation time reduces by a small amount with the patch, from around 16s without the patch to around 13s with the patch. The query time, however, dropped from around 26s to around 14s! Almost 2x speedup with the patch! Moreover, looking at the loop timing in the explain analyze output, it goes from about 7..24 ms per loop down to about 1.5..13 ms per loop. That seems to indicate that the index distribution is better, with more queries returning quickly. So, great work Alexander! Very convincing results. Marking ready for committer, but please apply my comment fixes at your discretion. Regards, Jeff Davis PS: the test was run on my workstation (Intel(R) Core(TM) i7-2600 CPU @ 3.40GHz) with work_mem=512MB, shared_buffers=512MB, and checkpoint_segments=32. The rest of the settings were default. range-gist-comments.patch.gz Description: GNU Zip compressed data \timing on drop table big; drop table small; create temp table tmp_foo(i int, ir int8range); insert into tmp_foo select g % 100, 'empty'::int8range from generate_series(1,5) g; insert into tmp_foo select g % 100, int8range(NULL,NULL) from generate_series(1,1) g; insert into tmp_foo select g % 100, int8range(NULL,((random()-0.5)*g*10)::int8) from generate_series(1,2) g; insert into tmp_foo select g % 100, int8range(((random()-0.5)*g*10)::int8,NULL) from generate_series(1,2) g; insert into tmp_foo select g % 100, int8range( (g*10 + 10*(random()-0.5))::int8, (g*10 + 10 + 10*(random()-0.5))::int8 ) from generate_series(1,100) g; create table big as select * from tmp_foo order by random(); drop table tmp_foo; create table tmp_foo(i int, ir int8range); insert into tmp_foo select g*1000 % 100, 'empty'::int8range from generate_series(1,50) g; insert into tmp_foo select g*1000 % 100, int8range( (g*10*1000 + 10*(random()-0.5))::int8, (g*10*1000 + 10 + 10*(random()-0.5))::int8 ) from generate_series(1,1000) g; create table small as select * from tmp_foo order by random(); drop table tmp_foo; vacuum; vacuum; vacuum; create index big_idx on big using gist (ir); analyze; set enable_bitmapscan=false; explain analyze select sum(upper(intersection) - lower(intersection)) from ( select small.ir * big.ir as intersection from small,big where small.ir big.ir and not lower_inf(big.ir) and not upper_inf(big.ir) ) s; set enable_bitmapscan to default; set enable_indexscan=false; explain analyze select sum(upper(intersection) - lower(intersection)) from ( select small.ir * big.ir as intersection from small,big where small.ir big.ir and not lower_inf(big.ir) and not upper_inf(big.ir) ) s; set enable_indexscan to default; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Mon, Jan 30, 2012 at 1:39 AM, Jeff Davis pg...@j-davis.com wrote: Thank you for the updates. I have a small patch attached. The only code change I made was very minor: I changed the constants used in the penalty function because your version used INFINITE_BOUND_PENALTY when adding an empty range, and that didn't quite make sense to me. If I'm mistaken you can leave it as-is. I also attached range-gist-test.sql, which I used for a performance test. I mix various types of ranges together in a larger table of 1.1M tuples. And then I create a smaller table that only contains normal ranges and empty ranges. There are two tests: 1. Create an index on the big table 2. Do a range join (using overlaps rather than equals) where the smaller table is on the outer side of a nested loop join and an index scan over the larger table on the inner. The index creation time reduces by a small amount with the patch, from around 16s without the patch to around 13s with the patch. The query time, however, dropped from around 26s to around 14s! Almost 2x speedup with the patch! Moreover, looking at the loop timing in the explain analyze output, it goes from about 7..24 ms per loop down to about 1.5..13 ms per loop. That seems to indicate that the index distribution is better, with more queries returning quickly. So, great work Alexander! Very convincing results. Great! Thank you for reviewing this patch! Marking ready for committer, but please apply my comment fixes at your discretion. Patch with your comment fixes is attached. - With best regards, Alexander Korotkov. rangetypegist-0.7.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Hi! New version of patch is attached. On Thu, Dec 22, 2011 at 11:46 AM, Jeff Davis pg...@j-davis.com wrote: A few comments: * In range_gist_picksplit, it would be nice to have a little bit more intuitive description of what's going on with the nonEmptyCount and nonInfCount numbers. For instance, it appears to depend on the fact that a range must either be in nonEmptyCount or in nonInfCount. Also, can you describe the reason you're multiplying by two and taking the absolute value? It seems to work, but I am missing the intuition behind those operations. total_count - 2*nonEmptyCount = (total_count - nonEmptyCount) - nonEmptyCount = emptyCount - nonEmptyCount So, it's really not evident. I've simplified it. * The penalty function is fairly hard to read still. At a high level, I think we're trying to accomplish a few things (in order from most to least important): - Keep normal ranges separate. - Avoid broadening the class of the original predicate (e.g. turning single-infinite into double-infinite). - Avoid broadening (as determined by subtype_diff) the original predicate. - Favor adding ranges to narrower original predicates. Do you agree? If so, perhaps we can attack those more directly and it might be a little more readable. Additionally, the arbitrary numbers might become a problem. Can we choose better constants there? They would still be arbitrary when compared with real numbers derived from subtype_diff, but maybe we can still do better than what's there. I've changes some comments and add constants for penalty values. * Regarding the leftover common entries that can go to either side: what is the delta measure trying to accomplish? When I work through some examples, it seems to favor putting larger common ranges on the left (small delta) and smaller common ranges on the right (smaller delta). Why is that good? Or did I misread the code? Intuitively, I would think that we'd want to push the ranges with lower upper bounds to the left and higher lower bounds to the right -- in other words, recurse. Obviously, we'd need to make sure it terminated at some point, but splitting the common entries does seem like a smaller version of the original problem. Thoughts? That was a bug. Actually, no abs is needed. Indeed it doesn't affect result significantly. - With best regards, Alexander Korotkov. rangetypegist-0.6.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Wed, 2011-12-14 at 01:04 +0400, Alexander Korotkov wrote:
Hi!
Thank you! Attached a few changes:
* Change ordinal to normal for clarity (at least to me).
* Some comment cleanup
* Change classes_groups to be an enum of SPLIT_LEFT and SPLIT_RIGHT,
rather than using 1 and 2.
* Changed the bounds_lower and bounds_upper variables into
by_lower and by_upper to indicate that the arrays are distinguished
by sort order. It was confusing me to read it otherwise.
A few comments:
* In range_gist_picksplit, it would be nice to have a little bit more
intuitive description of what's going on with the nonEmptyCount and
nonInfCount numbers. For instance, it appears to depend on the fact that
a range must either be in nonEmptyCount or in nonInfCount. Also, can you
describe the reason you're multiplying by two and taking the absolute
value? It seems to work, but I am missing the intuition behind those
operations.
* The penalty function is fairly hard to read still. At a high level, I
think we're trying to accomplish a few things (in order from most to
least important):
- Keep normal ranges separate.
- Avoid broadening the class of the original predicate (e.g. turning
single-infinite into double-infinite).
- Avoid broadening (as determined by subtype_diff) the original
predicate.
- Favor adding ranges to narrower original predicates.
Do you agree? If so, perhaps we can attack those more directly and it
might be a little more readable.
Additionally, the arbitrary numbers might become a problem. Can we
choose better constants there? They would still be arbitrary when
compared with real numbers derived from subtype_diff, but maybe we can
still do better than what's there.
* Regarding the leftover common entries that can go to either side:
what is the delta measure trying to accomplish? When I work through
some examples, it seems to favor putting larger common ranges on the
left (small delta) and smaller common ranges on the right (smaller
delta). Why is that good? Or did I misread the code?
Intuitively, I would think that we'd want to push the ranges with lower
upper bounds to the left and higher lower bounds to the right -- in
other words, recurse. Obviously, we'd need to make sure it terminated at
some point, but splitting the common entries does seem like a smaller
version of the original problem. Thoughts?
Thank you for the helpful comments! It took me a while to work through
the logic, but I would have been lost completely without the comments
around the double sorting split.
Regards,
Jeff Davis
*** a/src/backend/utils/adt/rangetypes_gist.c
--- b/src/backend/utils/adt/rangetypes_gist.c
***
*** 39,45
((RangeType *) DatumGetPointer(datumCopy(PointerGetDatum(r), \
false, -1)))
! /* Minimum accepted ratio of split */
#define LIMIT_RATIO 0.3
/* Helper macros to place an entry in the left or right group */
--- 39,49
((RangeType *) DatumGetPointer(datumCopy(PointerGetDatum(r), \
false, -1)))
! /*
! * Minimum accepted ratio of split for items of the same class. If the items
! * are of different classes, it will separate along those lines regardless of
! * the ratio.
! */
#define LIMIT_RATIO 0.3
/* Helper macros to place an entry in the left or right group */
***
*** 66,72
* GiST. Each unique combination of properties is a class. CLS_EMPTY cannot be
* combined with anything else.
*/
! #define CLS_ORDINAL 0 /* Ordinal ranges (no bits set) */
#define CLS_LOWER_INF 1 /* Lower bound is infinity */
#define CLS_UPPER_INF 2 /* Upper bound is infinity */
#define CLS_CONTAIN_EMPTY 4 /* Contains underlying empty ranges */
--- 70,76
* GiST. Each unique combination of properties is a class. CLS_EMPTY cannot be
* combined with anything else.
*/
! #define CLS_NORMAL 0 /* Normal ranges (no bits set) */
#define CLS_LOWER_INF 1 /* Lower bound is infinity */
#define CLS_UPPER_INF 2 /* Upper bound is infinity */
#define CLS_CONTAIN_EMPTY 4 /* Contains underlying empty ranges */
***
*** 76,81
--- 80,102
* of properties. CLS_EMPTY doesn't combine with
* anything else, so it's only 2^3 + 1. */
+ /*
+ * Auxiliary structure for picksplit based on single sorting.
+ */
+ typedef struct
+ {
+ int index;
+ RangeBound bound;
+ TypeCacheEntry *typcache;
+ } PickSplitSortItem;
+
+ /* place on left or right side of split? */
+ typedef enum
+ {
+ SPLIT_LEFT = 0, /* makes initialization to SPLIT_LEFT easier */
+ SPLIT_RIGHT
+ } SplitLR;
+
static RangeType *range_super_union(TypeCacheEntry *typcache, RangeType *r1,
RangeType *r2);
static bool range_gist_consistent_int(FmgrInfo *flinfo,
***
*** 97,103 static int sort_item_cmp(const void *a, const void *b);
static void range_gist_class_split(TypeCacheEntry *typcache,
GistEntryVector *entryvec,
GIST_SPLITVEC *v,
!
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Tue, 2011-12-20 at 13:22 +0400, Alexander Korotkov wrote: Hi! Studying this question little more I found that current approach of range indexing can be dramatically inefficient in some cases. It's not because of penalty or split implementation, but because of approach itself. Mapping intervals to two-dimensional space produce much better results in case of high-overlapping ranges and @, @ operators with low selectivity. Thank you for testing this. I agree that your approach is much better especially dealing with widely varying range sizes, etc. My approach really only tackled the simple (and hopefully common) case when the ranges are about the same size. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Hi!
Studying this question little more I found that current approach of range
indexing can be dramatically inefficient in some cases. It's not because of
penalty or split implementation, but because of approach itself. Mapping
intervals to two-dimensional space produce much better results in case of
high-overlapping ranges and @, @ operators with low selectivity.
There is a simple test case for proof of concept.
create table source as (select l, (l + s) as r from (select
(random()*1)::int as l, (random()*1000 + 1)::int s from
generate_series(1,100) g) x);
create table range_test as (select int4range(l,r) as x from source);
create table point_test as (select point(l,r) as x from source);
create index range_test_idx on range_test using gist (x);
create index point_test_idx on point_test using gist (x);
test=# explain (analyze, buffers) select * from range_test where x @
int4range(5000,5010);
QUERY PLAN
-
Bitmap Heap Scan on range_test (cost=40.31..2585.65 rows=1000 width=32)
(actual time=37.304..37.310 rows=2 loops=1)
Recheck Cond: (x @ '[5000,5010)'::int4range)
Buffers: shared hit=767
- Bitmap Index Scan on range_test_idx (cost=0.00..40.06 rows=1000
width=0) (actual time=37.288..37.288 rows=2 loops=1)
Index Cond: (x @ '[5000,5010)'::int4range)
Buffers: shared hit=765
Total runtime: 37.385 ms
(7 rows)
test=# explain (analyze, buffers) select * from point_test where x @
box(point(5000,5000),point(5010,5010));
QUERY PLAN
---
Bitmap Heap Scan on point_test (cost=44.36..2589.69 rows=1000 width=16)
(actual time=0.197..0.206 rows=2 loops=1)
Recheck Cond: (x @ '(5010,5010),(5000,5000)'::box)
Buffers: shared hit=5
- Bitmap Index Scan on point_test_idx (cost=0.00..44.11 rows=1000
width=0) (actual time=0.182..0.182 rows=2 loops=1)
Index Cond: (x @ '(5010,5010),(5000,5000)'::box)
Buffers: shared hit=3
Total runtime: 0.265 ms
(7 rows)
test=# explain (analyze, buffers) select * from range_test where x @
int4range(5000,5990);
QUERY PLAN
---
Bitmap Heap Scan on range_test (cost=40.31..2585.65 rows=1000 width=32)
(actual time=4.578..4.603
rows=5 loops=1)
Recheck Cond: (x @ '[5000,5990)'::int4range)
Buffers: shared hit=52
- Bitmap Index Scan on range_test_idx (cost=0.00..40.06 rows=1000
width=0) (actual time=4.561..4.561 rows=5 loops=1)
Index Cond: (x @ '[5000,5990)'::int4range)
Buffers: shared hit=47
Total runtime: 4.669 ms
(7 rows)
test=# explain (analyze, buffers) select * from point_test where x @
box(point('-inf'::float,5990),point(5000,'+inf'::float));
QUERY PLAN
---
Bitmap Heap Scan on point_test (cost=44.36..2589.69 rows=1000 width=16)
(actual time=0.328..0.353 rows=5 loops=1)
Recheck Cond: (x @ '(5000,inf),(-inf,5990)'::box)
Buffers: shared hit=8
- Bitmap Index Scan on point_test_idx (cost=0.00..44.11 rows=1000
width=0) (actual time=0.312..0.312 rows=5 loops=1)
Index Cond: (x @ '(5000,inf),(-inf,5990)'::box)
Buffers: shared hit=3
Total runtime: 0.419 ms
(7 rows)
If you like to learn more information about such mapping you can start from
here: http://www.comsis.org/ComSIS/Vol7No4/RegularPapers/paper16.pdf
Any thoughts?
-
With best regards,
Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 12/13/2011 04:04 PM, Alexander Korotkov wrote: On Mon, Dec 12, 2011 at 10:41 PM, Jeff Davis pg...@j-davis.com mailto:pg...@j-davis.com wrote: * There's a lot of code for range_gist_penalty. Rather than having special cases for all combinations of properties in the new an original, is it possible to use something a little simpler? Maybe just start the penalty at zero, and add something for each property of the predicate range that must be changed. The penalties added might vary, e.g., if the original range has an infinite lower bound, changing it to have an infinite upper bound might be a higher penalty. I belive it's possible to make it simplier. I've coded quite intuitively. Probably, we should select some representive datasets in order to determine which logic is reasonable by tests. That seems to be a sticking point; you mentioned before that finding larger data sets useful for your purposes was hard. I'm not sure where you'll find data fitting your needs here, but it seems difficult to validate all of what you've done so far without it. I'm going to mark this one returned and hope you can dig up something useful to nail this down. You might also describe what it is you're looking for better and see if anyone else has a suggestion. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Hi! On Mon, Dec 12, 2011 at 10:41 PM, Jeff Davis pg...@j-davis.com wrote: Thank you. I have attached a patch that's mostly just cleanup to this one. Thanks a lot for cleanup. Path with applied cleanup is attached. Comments: * You use the term ordinal range quite a lot, which I haven't heard before. Is that a mathematical term, or do you mean something more like ordinary? Actually I meant ordinal range to be finite, non-empty and non-contain-empty range. It's not mathematical term. Probably there is some better word for that, but my english is not strong enough :). * There's a lot of code for range_gist_penalty. Rather than having special cases for all combinations of properties in the new an original, is it possible to use something a little simpler? Maybe just start the penalty at zero, and add something for each property of the predicate range that must be changed. The penalties added might vary, e.g., if the original range has an infinite lower bound, changing it to have an infinite upper bound might be a higher penalty. I belive it's possible to make it simplier. I've coded quite intuitively. Probably, we should select some representive datasets in order to determine which logic is reasonable by tests. * It looks like LIMIT_RATIO is not always considered. Should it be? Yes, it's so. In this part I repeat logic of GiST with NULLs. It makes NULLs to be separated from non-NULLs even if it's produce worse ratio. I'm not sure about how it should be. It seems to be tradeoff between having some excess pages and having slightly worse tree. * You defined get/set_range_contain_empty, but didn't use them. I think this was a merge error, but I removed them. So now there are no changes in rangetypes.c. Ok, thanks. -- With best regards, Alexander Korotkov. rangetypegist-0.5.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sat, Dec 10, 2011 at 6:14 PM, Greg Smith g...@2ndquadrant.com wrote: On 12/02/2011 06:48 AM, Alexander Korotkov wrote: Rebased with head. Could you comment a little more on what changed? There were a couple of areas Tom commented on: -General code fixes Expensibe usage of Max macro is fixed in 0.5 version of patch. -pull out and apply the changes related to the RANGE_CONTAIN_EMPTY flag, and also remove the opclass entry It's already done by Tom. -Subdiff issues The third one sounded hard to deal with, so presumably nothing there. As I wrote before, I believe there is some limitation of current GiST interface. Most likely we're not going to change GiST interface now and have to do will solution of tradeoff. I think good way to do it is to select representive datasets and do some tests which will show which logic is more reasonable. Actually, I need some help with that, because I don't have enough of datasets. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Fri, 2011-12-02 at 15:48 +0400, Alexander Korotkov wrote: Rebased with head. Thank you. I have attached a patch that's mostly just cleanup to this one. Comments: * You use the term ordinal range quite a lot, which I haven't heard before. Is that a mathematical term, or do you mean something more like ordinary? * There's a lot of code for range_gist_penalty. Rather than having special cases for all combinations of properties in the new an original, is it possible to use something a little simpler? Maybe just start the penalty at zero, and add something for each property of the predicate range that must be changed. The penalties added might vary, e.g., if the original range has an infinite lower bound, changing it to have an infinite upper bound might be a higher penalty. * It looks like LIMIT_RATIO is not always considered. Should it be? * You defined get/set_range_contain_empty, but didn't use them. I think this was a merge error, but I removed them. So now there are no changes in rangetypes.c. Regards, Jeff Davis range_gist_cleanup.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 12/02/2011 06:48 AM, Alexander Korotkov wrote: Rebased with head. Could you comment a little more on what changed? There were a couple of areas Tom commented on: -General code fixes -pull out and apply the changes related to the RANGE_CONTAIN_EMPTY flag, and also remove the opclass entry -Subdiff issues The third one sounded hard to deal with, so presumably nothing there. I'm not sure if your updated rebase addresses either of those first two yet or not, or if it was just fixing bitrot from upstream code changes. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Rebased with head. -- With best regards, Alexander Korotkov. rangetypegist-0.4.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sat, Nov 26, 2011 at 11:11 AM, Jeff Davis pg...@j-davis.com wrote: There's been some significant change in rangetypes_gist.c, can you please rebase this patch? OK, rebased with head. -- With best regards, Alexander Korotkov. rangetypegist-0.3.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Alexander Korotkov aekorot...@gmail.com writes: On Sat, Nov 26, 2011 at 11:11 AM, Jeff Davis pg...@j-davis.com wrote: There's been some significant change in rangetypes_gist.c, can you please rebase this patch? OK, rebased with head. I looked at this patch a bit. I agree with the aspect of it that says let's add a flag bit so we can tell whether an upper GiST item includes any empty ranges; I think we really need that in order to make contained_by searches usable. However, I'm not so happy with the proposed rewrite of the penalty/picksplit functions. I see two problems there: 1. penalty is using both hard-wired penalty values (1.0, 2.0, etc) and values obtained from subtype_diff. This is not good, because you have no idea what scale the subtype differences will be expressed on. The hard-wired values could be greatly larger than range widths, or greatly smaller, resulting in randomly different index behavior. 2. It's too large/complicated. You're proposing to add nearly a thousand lines to rangetypes_gist.c, and I do not see any reason to think that this is so much better than what's there now as to justify that kind of increment in the code size. I saw your performance results, but one set of results on an arbitrary (not-real-world) test case doesn't prove a lot to me; and in particular it doesn't prove that we couldn't do as well with a much smaller and simpler patch. There are a lot of garden-variety coding problems, too, for instance here: + *penalty = Max(DatumGetFloat8(FunctionCall2( + subtype_diff, orig_lower.val, new_lower.val)), 0.0); which is going to uselessly call the subtype_diff function twice most of the time (Max() is only a macro), plus you left off the collation argument. But I don't think it's worth worrying about those until the big picture is correct, which I feel it isn't yet. Earlier in the thread you wrote: Questions: 1) I'm not sure about whether we need support of in GiST, because it always produces full index scan (except search for non-empty ranges). I was thinking the same thing; that opclass entry seems pretty darn useless. I propose to pull out and apply the changes related to the RANGE_CONTAIN_EMPTY flag, and also remove the opclass entry, because I think these are uncontroversial and in the nature of must fix quickly. The redesign of the penalty and picksplit functions should be discussed separately. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sun, Nov 27, 2011 at 10:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. penalty is using both hard-wired penalty values (1.0, 2.0, etc) and values obtained from subtype_diff. This is not good, because you have no idea what scale the subtype differences will be expressed on. The hard-wired values could be greatly larger than range widths, or greatly smaller, resulting in randomly different index behavior. Current GiST code only compare penalty values of inserting same tuple. And don't see why it may alters. So, values obtained from subtype_diff and hard-wired values would be never compared each other. 2. It's too large/complicated. You're proposing to add nearly a thousand lines to rangetypes_gist.c, and I do not see any reason to think that this is so much better than what's there now as to justify that kind of increment in the code size. I saw your performance results, but one set of results on an arbitrary (not-real-world) test case doesn't prove a lot to me; and in particular it doesn't prove that we couldn't do as well with a much smaller and simpler patch. I've tested double sorting split algorithm itself pretty much on synthetic datasets. See paper for details. Strategy of separation of different classes of ranges really need more testing. But obtaining large enough real-life datasets is pretty *problematic for me.* There are a lot of garden-variety coding problems, too, for instance here: + *penalty = Max(DatumGetFloat8(FunctionCall2( + subtype_diff, orig_lower.val, new_lower.val)), 0.0); which is going to uselessly call the subtype_diff function twice most of the time (Max() is only a macro), plus you left off the collation argument. But I don't think it's worth worrying about those until the big picture is correct, which I feel it isn't yet. Oh, I see. It will be fixed. I propose to pull out and apply the changes related to the RANGE_CONTAIN_EMPTY flag, and also remove the opclass entry, because I think these are uncontroversial and in the nature of must fix quickly. The redesign of the penalty and picksplit functions should be discussed separately. I think the same. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Alexander Korotkov aekorot...@gmail.com writes: On Sun, Nov 27, 2011 at 10:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. penalty is using both hard-wired penalty values (1.0, 2.0, etc) and values obtained from subtype_diff. This is not good, because you have no idea what scale the subtype differences will be expressed on. The hard-wired values could be greatly larger than range widths, or greatly smaller, resulting in randomly different index behavior. Current GiST code only compare penalty values of inserting same tuple. And don't see why it may alters. So, values obtained from subtype_diff and hard-wired values would be never compared each other. I see your point that we only need the penalty values to be comparable for the same new value, but I don't think that really answers my objection, because you've had to lobotomize the logic. As an example, if we have a new empty range to insert, and all the existing root-page entries are ordinary finite ranges, this code will throw up its hands and give them all the same 4.0 penalty value. Surely it would be better to attempt to pick the smallest (narrowest) existing range. But to do that, you have to pay attention to the subdiff value. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Mon, Nov 28, 2011 at 3:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: I see your point that we only need the penalty values to be comparable for the same new value, but I don't think that really answers my objection, because you've had to lobotomize the logic. As an example, if we have a new empty range to insert, and all the existing root-page entries are ordinary finite ranges, this code will throw up its hands and give them all the same 4.0 penalty value. Surely it would be better to attempt to pick the smallest (narrowest) existing range. But to do that, you have to pay attention to the subdiff value. I believe it's a problem of the current GiST interface. If using subdiff value as an penalty for insertion of empty range, we have to return 0 penalty for any entry with RANGE_CONTAIN_EMPTY flag. And for plain empty entry too without any chance to define priority between them. In my opinion solution is that penalty function should return vector of floats instead of single float. With current GiST interface we have to do will solution of handling some cases better and some cases worse. For example, GiST for boxes also suffers from interface limitation. In many papers I met recommendation to choose smallest box from boxes with same extention (it's not a rare situation to have multiple boxes with zero extention) for tuple insertion. But with current interface, we can't implement it. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Wed, 2011-11-09 at 20:24 +0400, Alexander Korotkov wrote: New version of GiST for range types patch is here. This version seems to be complete and ready for review. There's been some significant change in rangetypes_gist.c, can you please rebase this patch? I like the patch conceptually, though I'm still working through the details. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
During work on gist for range types I've faced with following problem: test=# select 'empty'::int4range !?; ERROR: operator does not exist: int4range !? LINE 1: select 'empty'::int4range !?; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. test=# select 'empty'::int4range ?; ERROR: operator does not exist: int4range ? LINE 1: select 'empty'::int4range ?; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. So, !? and ? operators are mentioned in documentation, but don't present in catalog. Are them just missed in the catalog or there is some more serious problem? -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Alexander Korotkov aekorot...@gmail.com writes: So, !? and ? operators are mentioned in documentation, but don't present in catalog. Are them just missed in the catalog or there is some more serious problem? IIRC, Heikki removed them from the final commit. Sounds like he missed some documentation. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
First version of GiST for range types patch is here. Comments refactoring testing are coming soon. -- With best regards, Alexander Korotkov. rangetypegist-0.1.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 07.11.2011 20:36, Tom Lane wrote: Alexander Korotkovaekorot...@gmail.com writes: So, !? and ? operators are mentioned in documentation, but don't present in catalog. Are them just missed in the catalog or there is some more serious problem? IIRC, Heikki removed them from the final commit. Sounds like he missed some documentation. Yep. Fixed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Wed, 2011-11-02 at 21:29 +0200, Heikki Linnakangas wrote: + else if (lower1.infinite || upper1.infinite) + length1 = 1.0/0.0; That seems wrong. I take it that the point is to set length1 to infinity? I reworked this in commit (on my private repo, of course): 6197fbffb00f729feba8082136801cdef5ac850e For the archives, it's essentially taking the difference on the left side of the range, and the difference on the right side of the range, and adding them together. There are just a lot of special cases for infinite boundaries, empty ranges, and the lack of a subtype_diff function. I think it's a little closer to what Alexander intended, which I think is an improvement. It should now be able to recognize that expanding [10,) into [0,) has a penalty of 10. PS. I note the docs still refer to subtype_float. I'll fix that before committing. Thank you. The only change I found strange was the test that used \c to reconnect; but I can't say that my solution was any better. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Wed, 2011-11-02 at 22:59 +0200, Heikki Linnakangas wrote: This seems to be coming from the selectivity estimation function. The selectivity function for @ is scalargtsel, which is usually used for scalar and =. That doesn't seem right. But what do we store in the statistics for range types in the first place, and what would be the right thing to do for selectivity estimation? I'll have to think more about that, and it depends on the operator. It seems like an easier problem for contains a point than contains another range or overlaps with another range. Right now I don't have a very good answer, and even for the contains a point case I'll have to think about the representation in pg_statistic. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 03.11.2011 10:42, Jeff Davis wrote: On Wed, 2011-11-02 at 22:59 +0200, Heikki Linnakangas wrote: This seems to be coming from the selectivity estimation function. The selectivity function for@ is scalargtsel, which is usually used for scalar and=. That doesn't seem right. But what do we store in the statistics for range types in the first place, and what would be the right thing to do for selectivity estimation? I'll have to think more about that, and it depends on the operator. It seems like an easier problem for contains a point than contains another range or overlaps with another range. Right now I don't have a very good answer, and even for the contains a point case I'll have to think about the representation in pg_statistic. I've committed this now, after some more cleanup. I removed the selectivity estimation functions from operators where they were bogus, so writing those is a clear TODO. But that can well be done as a separate patch. Thanks! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Nov 3, 2011, at 4:59 AM, Heikki Linnakangas wrote: I've committed this now, after some more cleanup. I removed the selectivity estimation functions from operators where they were bogus, so writing those is a clear TODO. But that can well be done as a separate patch. Thanks! Woo! Congrats Jeff. Awesome news. Very excited about this feature. Thanks for getting this in, Heikki. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Nov3, 2011, at 18:54 , David E. Wheeler wrote: On Nov 3, 2011, at 4:59 AM, Heikki Linnakangas wrote: I've committed this now, after some more cleanup. I removed the selectivity estimation functions from operators where they were bogus, so writing those is a clear TODO. But that can well be done as a separate patch. Thanks! Woo! Congrats Jeff. Awesome news. Very excited about this feature. Thanks for getting this in, Heikki. +1. Great work, guys! best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Thu, Nov 3, 2011 at 3:59 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I've committed this now, after some more cleanup. I removed the selectivity estimation functions from operators where they were bogus, so writing those is a clear TODO. But that can well be done as a separate patch. Cool! Patch with GiST on range types improvements from me will be soon. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 01.11.2011 06:33, Jeff Davis wrote: On Mon, 2011-10-24 at 15:05 +0400, Alexander Korotkov wrote: I think implementing subtype_diff for each datatype is ok. We could implement some universal function based on minus operator and casting to double precision. But such solution might be unacceptable in both predictability (operator and casting function might do not the things we expect) and performance. Done. Thanks, I'm looking into this now. + else if (lower1.infinite || upper1.infinite) + length1 = 1.0/0.0; That seems wrong. I take it that the point is to set length1 to infinity? PS. I note the docs still refer to subtype_float. I'll fix that before committing. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 01.11.2011 06:33, Jeff Davis wrote: + else if (lower1.infinite || upper1.infinite) + length1 = 1.0/0.0; That seems wrong. I take it that the point is to set length1 to infinity? Please use get_float[48]_infinity() or get_float[48]_nan(), as appropriate (I think the latter may be intended here), rather than making up your own way of getting those values. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 01.11.2011 06:33, Jeff Davis wrote: On Mon, 2011-10-24 at 15:05 +0400, Alexander Korotkov wrote: I think implementing subtype_diff for each datatype is ok. We could implement some universal function based on minus operator and casting to double precision. But such solution might be unacceptable in both predictability (operator and casting function might do not the things we expect) and performance. Done. Everything is complete in this patch with the exception of two optional things, which I still intend to do but might best be done in a separate commit: * support typmod for ranges * support casts between different range types Both of these things, I believe, require the introduction of an RangeCoerceExpr, similar to ArrayCoerceExpr. That's fine, but it creates a rather large diff, so it might be best left for a later commit. Using the test table from the rangetypes test case: postgres=# select * from test_range_gist where 10 @ ir; ERROR: unsupported type: 3904 This seems to be coming from the selectivity estimation function. The selectivity function for @ is scalargtsel, which is usually used for scalar and =. That doesn't seem right. But what do we store in the statistics for range types in the first place, and what would be the right thing to do for selectivity estimation? I'll dig deeper into this tomorrow... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On 02.11.2011 22:59, Heikki Linnakangas wrote: I'll dig deeper into this tomorrow... Forgot to mention: I have pushed what I have done this far to my git repository at git://git.postgresql.org/git/users/heikki/postgres.git, if you want to take a look. Nothing major, just garden-variety cleanup. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Mon, Oct 24, 2011 at 3:05 PM, Alexander Korotkov aekorot...@gmail.comwrote: If we allow user to specify own gist_penalty function, then such function should deal with: 1) GiST-specific data structures such as GISTENTRY. 2) Decomposing ranges using range_deserialize. 3) Inifinities, which we could handle in general penalty functions. Thats why I prefere to implement subtype_diff. I forgot another agument for having subtype_diff: 4) In my picksplit algorithm it would be more natural to use subtype_diff for measuring overlap than use penalty function. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
Hi! On Mon, Oct 17, 2011 at 12:38 PM, Jeff Davis pg...@j-davis.com wrote: I started implementing subtype_diff, and I noticed that it requires defining an extra function for each range type. Previously, the numeric types could just use a cast, which was convenient for user-defined range types. If you have any other ideas to make that cleaner, please let me know. Otherwise I'll just finish implementing subtype_diff. I think implementing subtype_diff for each datatype is ok. We could implement some universal function based on minus operator and casting to double precision. But such solution might be unacceptable in both *predictability (operator and casting function might do not the things we expect) and performance.* I'm beginning to think that we should just allow the user to specify their own gist_penalty function. Specifying just the subtype_diff doesn't save much time, and it can only be limiting. Additionally, it's harder for users to understand the purpose of the function. If we allow user to specify own gist_penalty function, then such function should deal with: 1) GiST-specific data structures such as GISTENTRY. 2) Decomposing ranges using range_deserialize. 3) Inifinities, which we could handle in general penalty functions. Thats why I prefere to implement subtype_diff. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sun, 2011-10-16 at 14:43 -0700, Jeff Davis wrote: On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote: The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but data distribution can be so that these bits are valuable. Wouldn't it better to have function like subtype_diff_float which returns difference between two values of subtype as an float? Using of such function could make penalty more sensible to even small difference between values, and accordingly more relevant. I started implementing subtype_diff, and I noticed that it requires defining an extra function for each range type. Previously, the numeric types could just use a cast, which was convenient for user-defined range types. If you have any other ideas to make that cleaner, please let me know. Otherwise I'll just finish implementing subtype_diff. I'm beginning to think that we should just allow the user to specify their own gist_penalty function. Specifying just the subtype_diff doesn't save much time, and it can only be limiting. Additionally, it's harder for users to understand the purpose of the function. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote: The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but data distribution can be so that these bits are valuable. Wouldn't it better to have function like subtype_diff_float which returns difference between two values of subtype as an float? Using of such function could make penalty more sensible to even small difference between values, and accordingly more relevant. I started implementing subtype_diff, and I noticed that it requires defining an extra function for each range type. Previously, the numeric types could just use a cast, which was convenient for user-defined range types. If you have any other ideas to make that cleaner, please let me know. Otherwise I'll just finish implementing subtype_diff. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote: The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but data distribution can be so that these bits are valuable. Wouldn't it better to have function like subtype_diff_float which returns difference between two values of subtype as an float? Using of such function could make penalty more sensible to even small difference between values, and accordingly more relevant. The reason I did it that way is for unbounded ranges. With subtype_diff_float, it's difficult for the GiST code to differentiate between [10,) and [10,), because infinity minus anything is infinity. But when inserting the range [100,200), the penalty for the first one should be zero and the second one should have some positive penalty, right? Maybe we can still use subtype_diff_float by calling it on various pairs of bounds to come up with a reasonable cost? I'm open to suggestion. I'd just like to make sure that unbounded ranges are a part of the consideration. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sat, Oct 8, 2011 at 1:01 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote: The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but data distribution can be so that these bits are valuable. Wouldn't it better to have function like subtype_diff_float which returns difference between two values of subtype as an float? Using of such function could make penalty more sensible to even small difference between values, and accordingly more relevant. The reason I did it that way is for unbounded ranges. With subtype_diff_float, it's difficult for the GiST code to differentiate between [10,) and [10,), because infinity minus anything is infinity. But when inserting the range [100,200), the penalty for the first one should be zero and the second one should have some positive penalty, right? I meant that penalty can be determined as sum of difference of old and new bounds of range, i.e. penalty = subtype_diff_float(new_lower, old_lower) + subtype_diff_float(old_upper, new_upper). When we insert [100,200) into [10,+inf), union([100,200), [10,+inf)) = [10,+inf), so penalty = subtype_diff_float(10,10) + subtype_diff_float(+inf, +inf) = 0 + 0 = 0. When we insert [100,200) into [10,), union([100,200), [10,+inf)) = [100,+inf), so penalty = subtype_diff_float(100,10) + subtype_diff_float(+inf, +inf) = 99900 + 0 = 99900. But, there are still the problem, when we'are inserting open interval when there is no such open intervals yet. For example, we're going to insert [0,+inf), while root page contains [0,10), [10,20), [20,30). Each penalty will be infinity, while it seems to be better to insert it into [0,10). But, it seems to me to be general limitation of current GiST interface, when we have to express penalty in a single float. -- With best regards, Alexander Korotkov.
Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Sat, 2011-10-08 at 18:43 +0400, Alexander Korotkov wrote: I meant that penalty can be determined as sum of difference of old and new bounds of range, i.e. penalty = subtype_diff_float(new_lower, old_lower) + subtype_diff_float(old_upper, new_upper). When we insert [100,200) into [10,+inf), union([100,200), [10,+inf)) = [10,+inf), so penalty = subtype_diff_float(10,10) + subtype_diff_float(+inf, +inf) = 0 + 0 = 0. When we insert [100,200) into [10,), union([100,200), [10, +inf)) = [100,+inf), so penalty = subtype_diff_float(100,10) + subtype_diff_float(+inf, +inf) = 99900 + 0 = 99900. OK, I like that. I will make the change. But, there are still the problem, when we'are inserting open interval when there is no such open intervals yet. For example, we're going to insert [0,+inf), while root page contains [0,10), [10,20), [20,30). Each penalty will be infinity, while it seems to be better to insert it into [0,10). But, it seems to me to be general limitation of current GiST interface, when we have to express penalty in a single float. That seems like an acceptable limitation. I don't think my solution handles it any better. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)
On Fri, Oct 7, 2011 at 7:41 AM, Jeff Davis pg...@j-davis.com wrote: I'd prefer to include it in the initial patch. If the current GiST code is going to be replaced, then there's not much sense reviewing/testing it. You may need to consider unbounded and empty ranges specially. I made an attempt to do so in the current GiST code, and you might want to take a look at that first. I'm not particularly attached to my approach, but we should do something reasonable with unbounded and empty ranges. The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but data distribution can be so that these bits are valuable. Wouldn't it better to have function like subtype_diff_float which returns difference between two values of subtype as an float? Using of such function could make penalty more sensible to even small difference between values, and accordingly more relevant. -- With best regards, Alexander Korotkov.
