On 11/05/04, Nick Barr (nicky@chuckie.co.uk) wrote:
> Rory Campbell-Lange wrote:
> > > Look carefully at your column types. I can see several smallint
> > > columns in there WHERE clause which are not expicitely typed as
> > > such.
> > I'm not sure how to do this, Paul. do I do b.n_id::smallint ? Is
> > smallint not implied?
> Not quite. Explicit casts are needed when you have any numbers in the
> WHERE condition and the columns are not of type integer/int4. For
> example I have tweaked your query.
...
> Note that b.n_creator and o.n_creator do not need explicit casts because
> they are both of type integer anyway. You could of course put them in
> for clarity. PG only casts the numbers to integer's, and not to smallint
> or bigint, which basically means it does not use any indexes on that
> column. This is fixed in 7.4 I believe, which you seem to be running
> anyway so you might not be affected.
Mmm. Seems like I should try profiling with and without the cast. Maybe
there is little value in defining a column as a smallint, other than
checking the length of input.
Thanks again for your help,
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
