Message-ID: <514ACA5A.2080401@vmware.com>
Date: Thu, 21 Mar 2013 10:52:42 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Vasilis Ventirozos <v.ventirozos@gmail.com>
CC: postgres performance list <pgsql-performance@postgresql.org>,
	Alexander Korotkov <aekorotkov@gmail.com>
Subject: Re: Index usage for tstzrange?
References: <514A5088.6060101@agliodbs.com> <1326.1363838330@sss.pgh.pa.us>
	<CAF8jcqpsvu8ckR8OLxVYconNm=vJLUg-YJa3XC81bGSVV3bBoA@mail.gmail.com>
In-Reply-To: 
 <CAF8jcqpsvu8ckR8OLxVYconNm=vJLUg-YJa3XC81bGSVV3bBoA@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------070201020706000200070101"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------070201020706000200070101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------070201020706000200070101
Content-Type: text/x-diff;
 name="fix-element-contained-by-range-op.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="fix-element-contained-by-range-op.patch"

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);
 	}

--------------070201020706000200070101
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--------------070201020706000200070101--