Subject: FAQ -- Index usage/speed

From: Thomas F.O'Connell <[email-masked]>
Date: Tue, 31 Aug 2004 11:30:45 -0500

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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 31 Aug 2004 15:05:54 -0400 (EDT)

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.

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

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

---

From: Thomas F.O'Connell <[email-masked]>
Date: Tue, 31 Aug 2004 14:12:30 -0500

I thought about that, and it seems like the upgrade path from 7.x -> 
8.x is going to be slower than among any of the 7.x releases. 8.0 is 
still several months away and it will probably be several months more 
before people who are affected by this issue in production databases.

The other thing I thought about was a caveat in the 7.x documentation 
under indexes, as it doesn't seem to be mentioned anywhere. That would 
keep it off the general FAQ but let folks using 7.x releases know about 
it.

-tfo

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 31 Aug 2004 15:17:17 -0400 (EDT)

> keep it off the general FAQ but let folks using 7.x releases know about 

It is on the TODO:

	* -Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
	  float4, numeric/decimal too

---

From: Josh Berkus <[email-masked]>
Date: Tue, 31 Aug 2004 20:11:28 -0700

Bruce,

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

I'd disagree.   We can expect people to be using 7.3 and 7.4 for 2 years yet.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---

From: "Marc G. Fournier" <[email-masked]>
Date: Wed, 1 Sep 2004 00:21:49 -0300 (ADT)

> I'd disagree.  We can expect people to be using 7.3 and 7.4 for 2 years 

Have to agree ... I'm still supporting clients using 7.2 ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: [email-masked]           Yahoo!: yscrappy              ICQ: 7615664

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 31 Aug 2004 23:28:26 -0400 (EDT)

> Have to agree ... I'm still supporting clients using 7.2 ...

OK, added to FAQ:

    <P>In pre-8.0 releases, indexes often can not be used unless the data
    types exactly match the index's column types.  This is particularly
    true of int2, int8, and numeric column indexes.</P>