Query plan: varchar vs char indexes

From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "pgsql-general" <pgsql-general(at)postgreSQL(dot)org>
Subject: Query plan: varchar vs char indexes
Date: 2001-01-31 16:21:59
Message-ID: 002001c08ba1$f1dd94c0$1001a8c0@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Attachment Content-Type Size
dbtest2.txt text/plain 8.1 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mwaples 2001-01-31 17:14:01 Re: php as stored procedures
Previous Message mwaples 2001-01-31 16:21:12 php as stored procedures