pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Using bigint needs explicit cast to use the index
Date: Mon, 8 Mar 2004 11:05:25 -0500
Message-ID: <20040308160525.GA25077@phlogiston.dyndns.org> (raw)
In-Reply-To: <001101c404a3$fb34d320$fd08a8c0@steve>
References: <001101c404a3$fb34d320$fd08a8c0@steve>

On Mon, Mar 08, 2004 at 10:26:21AM +1000, Steven Butler wrote:
> I tested my hunch by casting the constant to bigint (as can be seen below)
> and suddenly the query is using the index again.

Yes.  You can make this work all the time by quoting the constant. 
That is, instead of

	WHERE indexcolumn = 123

do

	WHERE indexcolumn = '123'

 
> We are currently using pg 7.3.4.  Is this intended behaviour?  Should the
> constant be cast to the type of the table column where possible, or should

"Intended", no.  "Expected", yes.  This topic has had the best
Postgres minds work on it, and so far nobody's come up with a
solution.  There was a proposal to put in a special-case automatic
fix for int4/int8 in 7.4, but I don't know whether it made it in.

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin



view thread (6+ messages)  latest in thread

Message-ID: <20040308160525.GA25077@phlogiston.dyndns.org>
Permalink:  ../20040308160525.GA25077@phlogiston.dyndns.org/
Also on:    postgresql.org/message-id/20040308160525.GA25077@phlogiston.dyndns.org

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ajs@crankycanuck.ca
  Subject: Re: Using bigint needs explicit cast to use the index
  In-Reply-To: <20040308160525.GA25077@phlogiston.dyndns.org>

* 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