Re: Huge Performance Difference on Similar Query in Pg7.2

On Fri, 22 Mar 2002, Nigel J. Andrews wrote:

> Spectacular! Bingo! etc.
>
> Using the numbers quoted yields use of the primary key. I am indeed using
> something other than int4, int2 in fact. So this is something to do with the
> using integer literals which are presumably first interpreted as int4 and then
> are converted in some long winded fashion, or something, to int2 for each and
> every test or row, whereas specifying them as text causes the backend to
> convert to the correct int2 only at the start?

Basically. The quoted one gets kept as unknown rather than prematurely
being made into an int4.

> I choose the smaller int because these are unlikely to be restrictive for this
> DB and I thought I may as well try and not waste space. Whether it used the
> same storage as int4 didn't really matter as I'd given it the opportunity to
> use less if it could. Is it worth me moving these to int4 type?

Probably unless the space is really going to be an issue. Eventually
someone will come up with a way of dealing with it that doesn't involve
hardcoding type information and it'll get gixed.

> BTW, is this sort of feature documented anywhere or does it come into the 'what
> makes a person an expert' catagory?

Hmm, I'm not sure. If it's not in the FAQ 4.8 it probably should be.