pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex usage for tstzrange?
9+ messages / 4 participants
[nested] [flat]
* Index usage for tstzrange?
@ 2013-03-21 00:12 Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2013-03-21 00:12 UTC (permalink / raw)
To: pgsql-performance; +Cc: Jeff Davis <pgsql@j-davis.com>
Folks,
I just noticed that if I use a tstzrange for convenience, a standard
btree index on a timestamp won't get used for it. Example:
table a (
id int,
val text,
ts timestamptz
);
index a_ts on a(ts);
SELECT * FROM a WHERE ts <@ tstzrange('2013-01-01','2013-01-01 00:10:00')
... will NOT use the index a_ts. Is this something which could be fixed
for 9.4?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
@ 2013-03-21 03:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-03-21 04:07 ` Re: Index usage for tstzrange? Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-22 00:05 ` Re: Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2013-03-21 03:58 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance; Jeff Davis <pgsql@j-davis.com>
Josh Berkus <josh@agliodbs.com> writes:
> I just noticed that if I use a tstzrange for convenience, a standard
> btree index on a timestamp won't get used for it. Example:
> table a (
> id int,
> val text,
> ts timestamptz
> );
> index a_ts on a(ts);
> SELECT * FROM a WHERE ts <@ tstzrange('2013-01-01','2013-01-01 00:10:00')
> ... will NOT use the index a_ts.
Well, no. <@ is not a btree-indexable operator.
What I find more disturbing is that this is what I get from the example
in HEAD:
regression=# explain SELECT * FROM a WHERE ts <@ tstzrange('2013-01-01','2013-01-01 00:10:00');
ERROR: XX000: type 1184 is not a range type
LOCATION: range_get_typcache, rangetypes.c:1451
Haven't traced through it to determine exactly what's happening, but
isn't this a legitimate usage? And if it isn't, surely a more
user-facing error ought to be getting thrown somewhere upstream of here.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-03-21 04:07 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-21 08:52 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
1 sibling, 1 reply; 9+ messages in thread
From: Vasilis Ventirozos @ 2013-03-21 04:07 UTC (permalink / raw)
To: pgsql-performance
On Thu, Mar 21, 2013 at 5:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > I just noticed that if I use a tstzrange for convenience, a standard
> > btree index on a timestamp won't get used for it. Example:
>
> > table a (
> > id int,
> > val text,
> > ts timestamptz
> > );
> > index a_ts on a(ts);
>
> > SELECT * FROM a WHERE ts <@ tstzrange('2013-01-01','2013-01-01 00:10:00')
>
> > ... will NOT use the index a_ts.
>
> Well, no. <@ is not a btree-indexable operator.
>
> What I find more disturbing is that this is what I get from the example
> in HEAD:
>
> regression=# explain SELECT * FROM a WHERE ts <@
> tstzrange('2013-01-01','2013-01-01 00:10:00');
> ERROR: XX000: type 1184 is not a range type
> LOCATION: range_get_typcache, rangetypes.c:1451
>
> Haven't traced through it to determine exactly what's happening, but
> isn't this a legitimate usage? And if it isn't, surely a more
> user-facing error ought to be getting thrown somewhere upstream of here.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
It is a legit usage, this is from a test i did myself (9.2.3)
test=# explain SELECT * FROM a WHERE ts <@
tstzrange('2013-01-01','2013-04-01 00:10:00');
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on a (cost=0.00..23.75 rows=1 width=44)
Filter: (ts <@ '["2013-01-01 00:00:00+02","2013-04-01
00:10:00+03")'::tstzrange)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
2013-03-21 04:07 ` Re: Index usage for tstzrange? Vasilis Ventirozos <v.ventirozos@gmail.com>
@ 2013-03-21 08:52 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-03-22 21:53 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2013-03-21 08:52 UTC (permalink / raw)
To: Vasilis Ventirozos <v.ventirozos@gmail.com>; +Cc: pgsql-performance; Alexander Korotkov <aekorotkov@gmail.com>
On 21.03.2013 06:07, Vasilis Ventirozos wrote:
> On Thu, Mar 21, 2013 at 5:58 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> What I find more disturbing is that this is what I get from the example
>> in HEAD:
>>
>> regression=# explain SELECT * FROM a WHERE ts<@
>> tstzrange('2013-01-01','2013-01-01 00:10:00');
>> ERROR: XX000: type 1184 is not a range type
>> LOCATION: range_get_typcache, rangetypes.c:1451
>>
>> Haven't traced through it to determine exactly what's happening, but
>> isn't this a legitimate usage? And if it isn't, surely a more
>> user-facing error ought to be getting thrown somewhere upstream of here.
>
> It is a legit usage, this is from a test i did myself (9.2.3)
>
> test=# explain SELECT * FROM a WHERE ts<@
> tstzrange('2013-01-01','2013-04-01 00:10:00');
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Seq Scan on a (cost=0.00..23.75 rows=1 width=44)
> Filter: (ts<@ '["2013-01-01 00:00:00+02","2013-04-01
> 00:10:00+03")'::tstzrange)
Looks like the range type cost estimation patch broke this, back in
August already. The case of var <@ constant, where constant is a range
and var is an element, that's broken. The cost estimation function,
rangesel(), incorrectly assumes that the 'var' is always a range type.
It's a bit worrying that no-one noticed until now. I'll add a test for
that operator to the rangetypes regression test.
The immediate fix is attached, but this made me realize that rangesel()
is still missing estimation for the "element <@ range" operator. It
shouldn't be hard to implement, I'm pretty sure we have all the
statistics we need for that.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
fix-element-contained-by-range-op.patch (text/x-diff, 1.0K) download
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index 76dc913..c450c6a 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -154,8 +154,6 @@ rangesel(PG_FUNCTION_ARGS)
}
}
- typcache = range_get_typcache(fcinfo, vardata.vartype);
-
/*
* OK, there's a Var and a Const we're dealing with here. We need the
* Const to be of same range type as the column, else we can't do anything
@@ -169,6 +167,8 @@ rangesel(PG_FUNCTION_ARGS)
*/
if (operator == OID_RANGE_CONTAINS_ELEM_OP)
{
+ typcache = range_get_typcache(fcinfo, vardata.vartype);
+
if (((Const *) other)->consttype == typcache->rngelemtype->type_id)
{
RangeBound lower, upper;
@@ -185,6 +185,8 @@ rangesel(PG_FUNCTION_ARGS)
}
else
{
+ typcache = range_get_typcache(fcinfo, ((Const *) other)->consttype);
+
if (((Const *) other)->consttype == vardata.vartype)
constrange = DatumGetRangeType(((Const *) other)->constvalue);
}
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
2013-03-21 04:07 ` Re: Index usage for tstzrange? Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-21 08:52 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2013-03-22 21:53 ` Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 0 replies; 9+ messages in thread
From: Heikki Linnakangas @ 2013-03-22 21:53 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Vasilis Ventirozos <v.ventirozos@gmail.com>; pgsql-performance
On 21.03.2013 17:55, Alexander Korotkov wrote:
> On Thu, Mar 21, 2013 at 12:52 PM, Heikki Linnakangas<
>> The immediate fix is attached, but this made me realize that rangesel() is
>> still missing estimation for the "element<@ range" operator. It shouldn't
>> be hard to implement, I'm pretty sure we have all the statistics we need
>> for that.
>
> Probably we could even call existing scalarltsel and scalargtsel for this
> case.
I came up with the attached. I didn't quite use scalarltsel, but I used
the scalarineqsel function, which contains the "guts" of scalarltsel and
scalargtsel.
One thing I wasn't quite sure of (from the patch):
> /*
> * We use the data type's default < operator. This is bogus, if the range
> * type's rngsubopc operator class is different. In practice, that ought
> * to be rare. It would also be bogus to use the < operator from the
> * rngsubopc operator class, because the statistics are collected using
> * using the default operator class, anyway.
> *
> * For the same reason, use the default collation. The statistics are
> * collected with the default collation.
> */
Does that make sense? The other option would be to use the < operator
from the rngsubopc op class, even though the scalar statistics are
collected with the default b-tree < operator. As long as the two sort
roughly the same way, you get reasonable results either way. Yet another
option would be to use histogram_selectivity() instead of
ineq_histogram_selectivity(), if the range's rngsubopc opclass isn't the
type's default opclass. histogram_selectivity() works with any operator
regardless of the sort ordering, basically using the histogram values
merely as a sample, rather than as a histogram. But I'm reluctant to
make this any more complicated, as using a non-default opclass for the
range type is rare.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
estimate-elem-contained-by-1.patch (text/x-diff, 6.7K) download
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index c450c6a..60700b8 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -55,6 +55,8 @@ static double calc_hist_selectivity_contains(TypeCacheEntry *typcache,
RangeBound *lower, RangeBound *upper,
RangeBound *hist_lower, int hist_nvalues,
Datum *length_hist_values, int length_hist_nvalues);
+static double calc_elem_contained_by_sel(PlannerInfo *root, TypeCacheEntry *typcache,
+ VariableStatData *vardata, RangeType *constval);
/*
* Returns a default selectivity estimate for given operator, when we don't
@@ -155,18 +157,35 @@ rangesel(PG_FUNCTION_ARGS)
}
/*
- * OK, there's a Var and a Const we're dealing with here. We need the
- * Const to be of same range type as the column, else we can't do anything
- * useful. (Such cases will likely fail at runtime, but here we'd rather
- * just return a default estimate.)
- *
- * If the operator is "range @> element", the constant should be of the
- * element type of the range column. Convert it to a range that includes
- * only that single point, so that we don't need special handling for
- * that in what follows.
+ * OK, there's a Var and a Const we're dealing with here. Check that the
+ * Const is of the right type, else we can't do anything useful. (Such
+ * cases will likely fail at runtime, but here we'd rather just return a
+ * default estimate.)
*/
- if (operator == OID_RANGE_CONTAINS_ELEM_OP)
+ if (operator == OID_RANGE_ELEM_CONTAINED_OP)
+ {
+ /*
+ * "element <@ range" is quite different from the other range
+ * operators, in that the Var is not a range, but of the element type.
+ */
+ typcache = range_get_typcache(fcinfo, ((Const *) other)->consttype);
+
+ if (typcache->rngelemtype->type_id == vardata.vartype)
+ {
+ constrange = DatumGetRangeType(((Const *) other)->constvalue);
+ selec = calc_elem_contained_by_sel(root, typcache, &vardata, constrange);
+ }
+ else
+ selec = default_range_selectivity(operator);
+ }
+ else if (operator == OID_RANGE_CONTAINS_ELEM_OP)
{
+ /*
+ * In "range @> element", the constant should be of the element type
+ * of the range column. Convert it to a range that includes only that
+ * single point, so that we don't need special handling for that in
+ * what follows.
+ */
typcache = range_get_typcache(fcinfo, vardata.vartype);
if (((Const *) other)->consttype == typcache->rngelemtype->type_id)
@@ -181,26 +200,29 @@ rangesel(PG_FUNCTION_ARGS)
upper.infinite = false;
upper.lower = false;
constrange = range_serialize(typcache, &lower, &upper, false);
+
+ selec = calc_rangesel(typcache, &vardata, constrange, operator);
}
+ else
+ selec = default_range_selectivity(operator);
}
else
{
- typcache = range_get_typcache(fcinfo, ((Const *) other)->consttype);
+ /*
+ * In all other range operators, both operands are ranges, and they
+ * must be of the same type.
+ */
+ typcache = range_get_typcache(fcinfo, vardata.vartype);
if (((Const *) other)->consttype == vardata.vartype)
+ {
constrange = DatumGetRangeType(((Const *) other)->constvalue);
+ selec = calc_rangesel(typcache, &vardata, constrange, operator);
+ }
+ else
+ selec = default_range_selectivity(operator);
}
- /*
- * If we got a valid constant on one side of the operator, proceed to
- * estimate using statistics. Otherwise punt and return a default
- * constant estimate.
- */
- if (constrange)
- selec = calc_rangesel(typcache, &vardata, constrange, operator);
- else
- selec = default_range_selectivity(operator);
-
ReleaseVariableStats(vardata);
CLAMP_PROBABILITY(selec);
@@ -1131,3 +1153,64 @@ calc_hist_selectivity_contains(TypeCacheEntry *typcache,
return sum_frac;
}
+
+/*
+ * Calculate selectivity of var <@ constant, where 'var' is of element type.
+ *
+ * This is equivalent to "var < lower AND var < upper". Use scalar estimation
+ * routines for that.
+ */
+static double
+calc_elem_contained_by_sel(PlannerInfo *root, TypeCacheEntry *typcache,
+ VariableStatData *vardata, RangeType *constval)
+{
+ Oid elem_lt_opr;
+ double lboundsel, uboundsel;
+ double selec;
+ RangeBound const_lower;
+ RangeBound const_upper;
+ bool empty;
+ TypeCacheEntry *elemtypcache;
+
+ /* Extract the bounds of the constant value. */
+ range_deserialize(typcache, constval, &const_lower, &const_upper, &empty);
+
+ if (empty)
+ {
+ /* an empty range contains nothing */
+ return 0.0;
+ }
+
+ /*
+ * We use the data type's default < operator. This is bogus, if the range
+ * type's rngsubopc operator class is different. In practice, that ought
+ * to be rare. It would also be bogus to use the < operator from the
+ * rngsubopc operator class, because the statistics are collected using
+ * using the default operator class, anyway.
+ *
+ * For the same reason, use the default collation. The statistics are
+ * collected with the default collation.
+ */
+ elemtypcache = lookup_type_cache(typcache->rngelemtype->type_id,
+ TYPECACHE_LT_OPR);
+ elem_lt_opr = elemtypcache->lt_opr;
+
+ /* estimate var < lower bound */
+ if (const_lower.infinite)
+ lboundsel = 0.0;
+ else
+ lboundsel = scalarineqsel(root, elem_lt_opr, false, vardata,
+ const_lower.val, elemtypcache->type_id);
+
+ /* estimate var < upper bound */
+ if (const_upper.infinite)
+ uboundsel = 1.0;
+ else
+ uboundsel = scalarineqsel(root, elem_lt_opr, false, vardata,
+ const_upper.val, elemtypcache->type_id);
+
+ /* lower < var < upper */
+ selec = uboundsel - lboundsel;
+
+ return selec;
+}
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 72c2c30..bf0d8b3 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -524,7 +524,7 @@ neqsel(PG_FUNCTION_ARGS)
* convert_to_scalar(). If it is applied to some other datatype,
* it will return a default estimate.
*/
-static double
+double
scalarineqsel(PlannerInfo *root, Oid operator, bool isgt,
VariableStatData *vardata, Datum constval, Oid consttype)
{
diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h
index 0c2cd34..cc3255c 100644
--- a/src/include/utils/selfuncs.h
+++ b/src/include/utils/selfuncs.h
@@ -129,6 +129,8 @@ extern double histogram_selectivity(VariableStatData *vardata, FmgrInfo *opproc,
Datum constval, bool varonleft,
int min_hist_size, int n_skip,
int *hist_size);
+extern double scalarineqsel(PlannerInfo *root, Oid operator, bool isgt,
+ VariableStatData *vardata, Datum constval, Oid consttype);
extern Pattern_Prefix_Status pattern_fixed_prefix(Const *patt,
Pattern_Type ptype,
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-03-22 00:05 ` Josh Berkus <josh@agliodbs.com>
2013-03-22 22:53 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
1 sibling, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2013-03-22 00:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance; Jeff Davis <pgsql@j-davis.com>
> Well, no. <@ is not a btree-indexable operator.
Yes, but it's equivalent to ( ( a >= b1 or b1 is null ) and ( a < b2 or
b2 is null ) ), which *is* btree-indexable and can use an index. So it
seems like the kind of optimization we could eventually make.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
2013-03-22 00:05 ` Re: Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
@ 2013-03-22 22:53 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-03-23 01:23 ` Re: Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-23 04:31 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Heikki Linnakangas @ 2013-03-22 22:53 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance; Jeff Davis <pgsql@j-davis.com>
On 22.03.2013 02:05, Josh Berkus wrote:
>> Well, no.<@ is not a btree-indexable operator.
>
> Yes, but it's equivalent to ( ( a>= b1 or b1 is null ) and ( a< b2 or
> b2 is null ) ), which *is* btree-indexable and can use an index. So it
> seems like the kind of optimization we could eventually make.
Yeah. The sort order of <@ is the same as regular b-tree, so it should
be possible. In fact, nothing stops you from creating the suitable
operator and b-tree support functions. See attached patch for int4, but
the same should work for timestamptz.
We should do this automatically. Or am I missing something?
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
btree-support-for-int4-contained-by-int4range.sql (text/x-sql, 830B) download
CREATE or replace FUNCTION btint4rangecmp(int4, int4range) RETURNS int4 AS
$$
SELECT CASE WHEN $1 < lower($2) THEN -1
WHEN $1 < upper($2) OR upper($2) IS NULL THEN 0
ELSE 1 END
$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE FUNCTION int4_contained_by_int4range (int4, int4range) RETURNS bool AS
'elem_contained_by_range' LANGUAGE internal IMMUTABLE STRICT;
CREATE OPERATOR <@ (
PROCEDURE=int4_contained_by_int4range,
LEFTARG=int4, RIGHTARG=int4range
);
ALTER OPERATOR FAMILY integer_ops USING btree ADD
OPERATOR 3 <@ (int4, int4range),
FUNCTION 1 btint4rangecmp(int4, int4range);
-- Create a test table
create table inttable (i int4 primary key);
insert into inttable select g from generate_series(1,100000) g;
-- Query it. Uses index, hooray!
select * from inttable where i <@ int4range(111, 120);
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
2013-03-22 00:05 ` Re: Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-22 22:53 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2013-03-23 01:23 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2013-03-23 01:23 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance; Jeff Davis <pgsql@j-davis.com>
> We should do this automatically. Or am I missing something?
Aside from the need to support @> as well, not that I can see.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index usage for tstzrange?
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Re: Index usage for tstzrange? Tom Lane <tgl@sss.pgh.pa.us>
2013-03-22 00:05 ` Re: Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-22 22:53 ` Re: Index usage for tstzrange? Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2013-03-23 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2013-03-23 04:31 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance; Jeff Davis <pgsql@j-davis.com>
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> We should do this automatically. Or am I missing something?
Yes. This is not equality.
> ALTER OPERATOR FAMILY integer_ops USING btree ADD
> OPERATOR 3 <@ (int4, int4range),
> FUNCTION 1 btint4rangecmp(int4, int4range);
That will break approximately everything in sight, starting with the
planner's opinion of what equality is. There is *way* too much stuff
that knows the semantics of btree opclasses for us to start jamming
random operators into them, even if this seemed to work in trivial
testing. (See the last section of src/backend/access/nbtree/README
to just scratch the surface of the assumptions this breaks.)
It's possible that for constant ranges we could have the planner expand
"intcol <@ 'x,y'::int4range" into "intcol between x and y", using
something similar to the index LIKE optimization (ie, the "special
operator" stuff in indxpath.c). I'd like to find a way to make that
type of optimization pluggable, though --- the existing approach of
hard-wiring knowledge into indxpath.c has never been anything but
a kluge, and it definitely doesn't scale as-is to anything except
built-in types and operators.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-03-23 04:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-21 00:12 Index usage for tstzrange? Josh Berkus <josh@agliodbs.com>
2013-03-21 03:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-03-21 04:07 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2013-03-21 08:52 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-03-22 21:53 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-03-22 00:05 ` Josh Berkus <josh@agliodbs.com>
2013-03-22 22:53 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2013-03-23 01:23 ` Josh Berkus <josh@agliodbs.com>
2013-03-23 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox