Skip site navigation (1) Skip section navigation (2)

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

From: "Ryan" <pgsql-performance(at)seahat(dot)com>
To: <bruno(at)wolff(dot)to>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Yet another 'why does it not use my index' question.
Date: 2003-05-07 14:43:28
Message-ID: 10192.65.102.128.233.1052318608.squirrel@fordparts.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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


In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-05-07 15:26:52
Subject: Re: An unresolved performance problem.
Previous:From: Tom LaneDate: 2003-05-07 14:19:25
Subject: Re: [PERFORM] Hypothetical suggestions for planner, indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group