public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thomas F.O'Connell <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: FAQ -- Index usage/speed
Date: Tue, 31 Aug 2004 11:30:45 -0500
Message-ID: <[email protected]> (raw)

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

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