Thread: Impact of varchar/text in use of indexes

Impact of varchar/text in use of indexes

From
Mike Moran
Date:
Hi. I have two existing tables, A and B. A has a 'varchar(1000)' field
and B has a 'text' field, each with btree indexes defined. When I do a
join between these, on this field, it seems to a hash join, as opposed
to using the indexes, as I might expect (I'm no postgres expert, btw).

My question is: if I changed both fields to be text or varchar(1000)
then would the index be used?

Ta,

--
Mike



Re: Impact of varchar/text in use of indexes

From
Tom Lane
Date:
Mike Moran <mike_moran@mac.com> writes:
> Hi. I have two existing tables, A and B. A has a 'varchar(1000)' field
> and B has a 'text' field, each with btree indexes defined. When I do a
> join between these, on this field, it seems to a hash join, as opposed
> to using the indexes, as I might expect (I'm no postgres expert, btw).

> My question is: if I changed both fields to be text or varchar(1000)
> then would the index be used?

Probably not, and in any case your assumption is mistaken.  Indexes are
not always the right way to join.

            regards, tom lane