Re: Very slow query

On 11/05/04, Nick Barr (nicky(at)chuckie(dot)co(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>