Re: Query plan: varchar vs char indexes

"Richard Huxton" <dev(at)archonet(dot)com> writes:
> With indexed varchar fields the explain changes - performing a seq-scan on
> users rather than using the index.
> Is this because the estimator guesses costs differently for char vs varchar
> or is it because I'm not explicitly casting the id fields?

The estimator has no special ideas about either char or varchar.
However there are some discrepancies in the sets of available functions
for the two datatypes, so what appears to be the same expression may
translate into different function invocations --- especially if you are
doing random combinations of datatypes and expecting the system to pick
an operator for you. I suspect it is picking a combination that doesn't
work out to be semantically equivalent to the '=' operator in the
index's operator class, so it doesn't think it can use the index.

> And yes - I know I probably shouldn't be joining an int to a varchar.

Not without being pretty darn careful. You didn't actually say what the
datatype of tag_list.id is, however.

regards, tom lane