public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bruce Momjian <[email protected]>
To: Thomas F. O'Connell <[email protected]>
Cc: [email protected]
Subject: Re: FAQ -- Index usage/speed
Date: Tue, 31 Aug 2004 15:05:54 -0400 (EDT)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>


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
  [email protected]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

view thread (7+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: FAQ -- Index usage/speed
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox