Thread: Query plan: varchar vs char indexes

The query is a join over 6 tables from PHP. I was comparing speeds and got
the following results:

mysql              : 2.90 requests/sec
pg (char fields)   : 3.04
pg (varchar fields): 0.71

Now, yipee for postgres in the second case, but I translated the char(nn)
fields to varchar(nn) because I was fetching loads of space-padding.
Performance drops by a factor of 4!

With indexed varchar fields the explain changes - performing a seq-scan on
users rather than using the index.

Now - the "id" field is an int for all tables other than users where it is
either char or varchar. No problem - pg casts everything appropriately but
making this field char rather than varchar makes the difference above (see
attached for details).

Is this because the estimator guesses costs differently for char vs varchar
or is it because I'm not explicitly casting the id fields?

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

Version 7.1beta3 on linux - query is CPU not disk bound.

TIA

- Richard Huxton

"Richard Huxton" <dev@archonet.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