Re: problem: index on number not honoured

This is an instance of a known problem: numeric constants get resolved to
'float8' during the parsing stage, so the planner doesn't know it can
use the 'int2' (or whatever) index for this query. The 'string like'
constants (i.e. anything quoted with ') are kept as 'unknown' until the
last stages, when the planner can then attempt to resolve them to match
the type of the underlying column/index.

It is interesting to see your comments regarding the JDBC interface: I
haven't seen that angle reported before. To date, it's been a matter of
suggesting workarounds to individual cases. The correct solution i(which
has arisen out of discussion on the HACKERS list: I think it's Tom Lane's
idea) is probably to come up with the concept of an 'unknown numeric'
constant, and treat it like the string constant gets treated. We've seen
a rash of these problems recently: unfortunately, this isn't fixed in
7.2 (which is in Beta 2 right now: should go Release Candidate1 tomorrow),
but I'd suggest it's a candidate for fixing early in 7.3. Might even
be argued as a 'bug fix', but the changes needed to fix this right are 
probably so extensive that it'll never go into a stable release. 

From the JDBC side, it _might_ be possible to put in a kludge to do the
quoting for you, well commented so it get's taken out when the backend
get's fixed. The JDBC driver has it's own release cycle, so that might
go in sooner than a 7.3 release ...

P.S. I'm copying HACKERS on this, to let the core know about the impact
on code written to use the JDBC driver.

Ross

On Mon, Nov 12, 2001 at 09:06:16PM +0100, Ilker Egilmez wrote:
> 
> hi,
> 
> an index on a table column of any number type only gets honoured if you
> query it like a string, e.g.
> 
> create table t1 ( n int2 ) ;
> 
> create index t1n on t1 (n) ;
> 
> explain select * from t1 where n = 1 ;
> 
> -- Seq Scan on t1  (cost=0.00..22.50 rows=10 width=2)
> 
> explain select * from t1 where n = '1'  ;
> 
> -- Index Scan using t1n on t1  (cost=0.00..8.14 rows=10 width=2)
> 
> first i thought this might be an psql client error and tried the same via
> jdbc, and look, there it happens again. if i create a PreparedStatemnt and
> bind the INT or LONG value with setLong (1,x) the index won't be used in the
> select statement. if i bind the value with a setString (1,x+"") command,
> then the index is honored correctly. I tested the code against postgres
> 7.1.3 as well as 7.0.2. this means that i would have to change all my java
> code from setLong to setString in order to speed up my apps every time i
> query a number. quite ugly!
> 
> ilker -)
> 
> 
> --
> --
> gate5 AG
> schoenhauser allee 62
> 10437 berlin
> 
> fon + 49 30 446 76 0
> fax + 49 30 446 76 555
> 
> http://www.gate5.de/ | ilker@gate5.de
> 
> 
> 
> --
> --
> gate5 AG
> schoenhauser allee 62
> 10437 berlin
> 
> fon + 49 30 446 76 0
> fax + 49 30 446 76 555
> 
> http://www.gate5.de/ | ilker@gate5.de
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html