From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Vasilis Ventirozos <v.ventirozos@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Subject: Re: Index usage for tstzrange?
Date: Thu, 21 Mar 2013 10:52:42 +0200
Message-ID: <514ACA5A.2080401@vmware.com> (raw)
In-Reply-To: <CAF8jcqpsvu8ckR8OLxVYconNm=vJLUg-YJa3XC81bGSVV3bBoA@mail.gmail.com>
References: <514A5088.6060101@agliodbs.com>
<1326.1363838330@sss.pgh.pa.us>
<CAF8jcqpsvu8ckR8OLxVYconNm=vJLUg-YJa3XC81bGSVV3bBoA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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-performanceAttachments:fix-element-contained-by-range-op.patch (text/x-diff, 1.0K) downloaddiff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.cindex 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);
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, v.ventirozos@gmail.com, aekorotkov@gmail.com
Subject: Re: Index usage for tstzrange?
In-Reply-To: <514ACA5A.2080401@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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