Re: PostgreSQL Indexing versus MySQL

On Wed, 18 Feb 2004, Lincoln Yeoh wrote:

> At 04:14 PM 2/17/2004 -0700, scott.marlowe wrote:
> >custom type (hex, foobar, etc...) from one to another. What this means
> >too you, the user, is that:
> >
> >create table test (id int8, info text);
> ><insert 10,000 rows>
> >select * from test where id=456;
> >
> >will result in a sequential scan. Why? Because the default integer type
> >is int4, and your id field is int8. Cast the value to int8, and watch it
> >use an index scan:
> >
> >select * From test where id=cast(456 as int8);
>
> Actually won't
> select * from test where id='456'
> use the index?
>
> I'm curious if this work in all cases - e.g. postgresql figures the best
> cast for text to whatever, even for relevant custom types?

It works, I just like writing things in the most self documenting manner
possible, since one day somebody else may look at:

select * from test where id='456'

and go, "hey, that's just an int, no need for the quotes" and take them
out not knowing what they do. cast(456 as int8) is pretty obvious, '456'
is much more subtle.