Thread: auto-casting a bit more intelligent in 8.0 ... ?

Just had a "performance" question from a client ... the query they were 
running, through EXPLAIN ANALYZE, was showing 37ms to resolve ... there 
are indices on the two fields being queried, but only used on one of them 
...

by changing the query *very slightly* (adding ::bigint to force-cast one 
of the where clauses), I brought it down to .1ms ...

the field in the table is defined as bigint, \d on the index shows its an 
index on bigint, but the query does it as:

field = 0

so, its trying bigint = int, and not picking up the index ...

the database server is 7.4.3 ... is this something that has been made a 
bit more intelligent in 8.0?  other then casting 0 to bigint, is there 
something else I should be looking at doing?  Having them go through all 
of their code and casting all ints to bigint for this sounds a bit extreme 
:(

Thanks ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> so, its trying bigint = int, and not picking up the index ...

> the database server is 7.4.3 ... is this something that has been made a 
> bit more intelligent in 8.0?  other then casting 0 to bigint, is there 
> something else I should be looking at doing?  Having them go through all 
> of their code and casting all ints to bigint for this sounds a bit extreme 
> :(

Yes, no, and no kidding ;-).  We've been getting beat on for *years*
about this behavior, but it took the proverbial Good Idea to find a
workable solution.  Can't generate those on a schedule I'm afraid...
        regards, tom lane