pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Thomas <paul@tmsl.demon.co.uk>
Cc: pgsql-performance @ postgresql . org <pgsql-performance@postgresql.org>
Subject: Re: Attempt at work around of int4 query won't touch int8 index ...
Date: Tue, 16 Sep 2003 10:05:06 -0400
Message-ID: <16841.1063721106@sss.pgh.pa.us> (raw)
In-Reply-To: <20030916084448.A24702@bacon>
References: <2756862E-E3F5-11D7-8513-000A9566A412@socialserve.com>
	<20901.1063248257@sss.pgh.pa.us>
	<3F65BABC.32672.38837688@localhost>
	<20030916084448.A24702@bacon>

Paul Thomas <paul@tmsl.demon.co.uk> 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



view thread (5+ messages)

Message-ID: <16841.1063721106@sss.pgh.pa.us>
Permalink:  ../16841.1063721106@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16841.1063721106@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, paul@tmsl.demon.co.uk
  Subject: Re: Attempt at work around of int4 query won't touch int8 index ...
  In-Reply-To: <16841.1063721106@sss.pgh.pa.us>
  References: <2756862E-E3F5-11D7-8513-000A9566A412@socialserve.com> <20901.1063248257@sss.pgh.pa.us> <3F65BABC.32672.38837688@localhost> <20030916084448.A24702@bacon> <16841.1063721106@sss.pgh.pa.us>

* 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