Re: Yet another 'why does it not use my index' question.

> On Wed, May 07, 2003 at 09:11:49 -0500,
> Ryan <pgsql-performance(at)seahat(dot)com> wrote:
>> I wanted to do the following:
>>
>> midas=# explain analyze select * from zip where zip in
>> (select option_value from client_options where option_name =
>> 'ZIP_CODE' );
>
> Until 7.4 comes out IN will be slow and you should use a join to do
> this.
>
>> midas=# explain analyze select * from zip z, client_options c where
>> c.option_name = 'ZIP_CODE' and c.option_value = z.zip;
>
> I think the problem here might be related to option_value being text and
> zip being char varying. This might prevent an index from being used to
> do the join.
HMMMM. I'll have to re-insert that table (it was a dbf2pg job) and change
that. Any reason why postgres is so picky about varchar/text conversion,
considering they are practally the same thing?

Something intresting however. If I do this:
select * from zip where zip = 98404;
I get a seq scan, as postgres types it to text.

but if I do this:
select * from zip where zip = '98404';
Postgres types it as character varying and uses the index.

Not that it would happen any time soon, but it would be nice if explain
analyze would tell you why it chose an seq scan on an indexed field.
(e.g. You should know better than to try an index with a different type!)

Ryan