pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problem: index on number not honoured
3+ messages / 3 participants
[nested] [flat]

* problem: index on number not honoured
@ 2001-11-12 20:06  Ilker Egilmez <ilker@gate5.de>
  0 siblings, 2 replies; 3+ messages in thread

From: Ilker Egilmez @ 2001-11-12 20:06 UTC (permalink / raw)
  To: pgsql-sql


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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: problem: index on number not honoured
@ 2001-11-15 16:26  Roland Roberts <roland@astrofoto.org>
  parent: Ilker Egilmez <ilker@gate5.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Roland Roberts @ 2001-11-15 16:26 UTC (permalink / raw)
  To: Ilker Egilmez <ilker@gate5.de>; +Cc: pgsql-sql

>>>>> "Ilker" == Ilker Egilmez <ilker@gate5.de> writes:


    Ilker> an index on a table column of any number type only gets honoured if you
    Ilker> query it like a string, e.g.

    Ilker> create table t1 ( n int2 ) ;

    Ilker> create index t1n on t1 (n) ;

    Ilker> explain select * from t1 where n = 1 ;

    Ilker> -- Seq Scan on t1  (cost=0.00..22.50 rows=10 width=2)

    Ilker> explain select * from t1 where n = '1'  ;

    Ilker> -- Index Scan using t1n on t1  (cost=0.00..8.14 rows=10 width=2)

Two questions: have you run vacuum analyze on the table?  have you
submitted a bug report?

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: problem: index on number not honoured
@ 2001-11-15 17:29  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Ilker Egilmez <ilker@gate5.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Ross J. Reedstrom @ 2001-11-15 17:29 UTC (permalink / raw)
  To: pgsql-sql; +Cc: pgsql-hackers@postgresql.org

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. 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-11-15 17:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-12 20:06 problem: index on number not honoured Ilker Egilmez <ilker@gate5.de>
2001-11-15 16:26 ` Roland Roberts <roland@astrofoto.org>
2001-11-15 17:29 ` Ross J. Reedstrom <reedstrm@rice.edu>

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