Re: Very slow query - Mailing list pgsql-general

From Rory Campbell-Lange
Subject Re: Very slow query
Date
Msg-id 20040511100525.GB30000@campbell-lange.net
Whole thread Raw
In response to Re: Very slow query  (Nick Barr <nicky@chuckie.co.uk>)
List pgsql-general
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>

pgsql-general by date:

Previous
From: Nick Barr
Date:
Subject: Re: Very slow query
Next
From: Reynir Þór Hübner
Date:
Subject: JDBC problem