Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
Paul Thomas <[EMAIL PROTECTED]> writes: > On 15/09/2003 08:42 Shridhar Daithankar wrote: >> Is it possible to follow data type upgrade model in planner? We have one, more or less. It's not explicitly coded, it emerges from the fact that certain casts are implicit and others are not. For instance, int4->float8 is implicit but float8->int4 is not. > Interestingly, float8 indexes do work OK (float8col = 99). I spend a large > part of yesterday grepping through the sources to try and find out why > this should be so. No luck so far but I'm going to keep on trying! The reason that case works is that there is no float8 = int4 operator. The parser can find no other interpretation than promoting the int4 to float8 and using float8 = float8. (The dual possibility, coerce float8 to int4 and use int4 = int4, is not considered because that coercion direction is not implicit.) So you end up with an operator that matches the float8 index, and all is well. The int8 case fails because there is a cross-type operator int8 = int4, and the parser prefers that since it's an exact match to the initial data types. But it doesn't match the int8 index. We've floated various proposals for solving this, such as getting rid of cross-type operators, but none so far have passed the test of not having bad side-effects. See the pg_hackers archives for details (and *please* don't waste this list's bandwidth with speculating about solutions until you've absorbed some of the history. This topic has been heard of before ;-).) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
On 15/09/2003 08:42 Shridhar Daithankar wrote: Is it possible to follow data type upgrade model in planner? Something like in C/C++ where data types are promoted upwards to find out better plan? int2->int4->int8->float4->float8 types. That could be a clean solution.. just a thought.. Interestingly, float8 indexes do work OK (float8col = 99). I spend a large part of yesterday grepping through the sources to try and find out why this should be so. No luck so far but I'm going to keep on trying! -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
On 10 Sep 2003 at 22:44, Tom Lane wrote: > James Robinson <[EMAIL PROTECTED]> writes: > > Is this just a dead end, or is there some variation of this that might > > possibly work, so that ultimately an undoctored literal number, when > > applied to an int8 column, could find an index? > > I think it's a dead end. What I was playing with this afternoon was > removing the int8-and-int4 comparison operators from pg_operator. > It works as far as making "int8col = 42" do the right thing, but I'm > not sure yet about side-effects. Is it possible to follow data type upgrade model in planner? Something like in C/C++ where data types are promoted upwards to find out better plan? int2->int4->int8->float4->float8 types. That could be a clean solution.. just a thought.. Bye Shridhar -- Hlade's Law:If you have a difficult task, give it to a lazy person -- they will find an easier way to do it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
James Robinson <[EMAIL PROTECTED]> writes: > Is this just a dead end, or is there some variation of this that might > possibly work, so that ultimately an undoctored literal number, when > applied to an int8 column, could find an index? I think it's a dead end. What I was playing with this afternoon was removing the int8-and-int4 comparison operators from pg_operator. It works as far as making "int8col = 42" do the right thing, but I'm not sure yet about side-effects. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
