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