Re: Non-unique index performance

On Fri, Jun 24, 2005 at 11:23:54AM +0100, Richard Huxton wrote:
> >I qouted them to use indexes. The other method is type casting the
> >values to indexed column type. I prefer the quoting method.
>
> Sorry - this is just plain wrong.
>
> If you had an int8 column and a value such as 17, then PG looked at 17
> and said Ha! an int4. Then it would not use your index. In such cases
> you could either cast the value to int8, or quote it (so that the
> planner decided its type later in the process).
>
> This was never required if you had a value that was large enough to be
> int8 but not int4, nor when the column was int4. It is not an issue at
> all as of version 8.0.
>
> So - if the column is a plain old int4 - just do things normally.

Well, I'd have to disagree. If you're handcrafting every query and you
know the types of every column and you're confident they're not going
to change, then yes, you could leave off the quotes.

OTOH, if you want to be consistant, or you can't be sure your caller
passed the right type or you just don't know anything about the table
or function you're using, quoting is a simple effective method to make
sure you get it right. It costs nothing and is a hell of a lot easier.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.