From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200408311905.i7VJ5sv16159@candle.pha.pa.us>
Subject: Re: FAQ -- Index usage/speed
In-Reply-To: <1BDAA016-FB6B-11D8-A844-000D93AE0944@sitening.com>
To: "Thomas F. O'Connell" <tfo@sitening.com>
Date: Tue, 31 Aug 2004 15:05:54 -0400 (EDT)
Cc: pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


Yes, 4.8 would be the right spot.  Not sure why we got so many reports
recently though.

However, with this fixed in 8.0, it probably isn't worth adding to the
FAQ.

---------------------------------------------------------------------------

Thomas F. O'Connell wrote:
> Bruce,
> 
> Considering the activity on the lists (at least recently and, I think, 
> historically) about postgres not casting (usually integer) constant 
> values across types, could there be a mention of this made in the FAQ? 
> It seems like a logical case for inclusion under 4.8:
> 
> http://www.postgresql.org/docs/faqs/FAQ.html#4.8
> 
> I was thinking something like the following:
> 
> Also note that 7.x versions of postgres will not automatically cast 
> constant data in certain queries such that an index would be used. For 
> example, if you have the following:
> 
> CREATE TABLE index_breaker (
> 	bigintcol		int8 primary key
> 	some_data 	text
> );
> 
> The following query is liikely to perform a sequential scan:
> 
> SELECT some_data FROM index_breaker WHERE bigintcol = 42;
> 
> postgres will interpret the constant value as a basic int and will thus 
> not use the index (implicitly created by the primary key) on the 
> bigintcol column.
> 
> There are some workarounds for this issue [per Tom Lane]:
> 
> 1. Always quote your constants:
> 
> 	... WHERE bigintcol = '42';
> 
> Similarly, constants can be explicitly cast:
> 
> 	... WHERE bigintcol = int8( 42 )
> 
> 2. Use a prepared statement:
> 
> 	PREPARE foo(bigint) AS ... WHERE bigintcol = $1;
> 	EXECUTE foo(42);
> 
> 3. Use parameterized statements in extended-query mode (essentially the 
> same idea as #2, but at the protocol level).  This doesn't help for 
> pure SQL scripts, but is very workable when coding against libpq or 
> JDBC.  Among other things it gets you out of worrying about SQL 
> injection attacks when your parameter values come from untrusted 
> sources.
> 
> 
> Technical improvements to wording are welcome. But I think this is 
> worth adding to the docs somewhere.
> 
> Thanks!
> 
> -tfo
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073