ive been evaluating pgsql as a replacement for MySQL, which currently
handles all of a client's authentication needs for their websites.
however, some queries that have been blindingly fast under MySQL are
incredlbly slow under pgsql.
for instance:
---[ CUT ]---
SELECT
doms.dom, types.batch, types.active, codes.code
FROM
doms,types,codes,hosts
WHERE
hosts.client=doms.client AND
doms.client=types.client AND
types.batch='FREECODE' AND
types.type=codes.type AND
hosts.hostname='somehostnamehere.com';
---[ CUT ]---
under MySQL, this query takes about 2-3 seconds. under pgsql v6.3, this
query takes roughly 40 seconds to a minute. system is a P133, Linux
kernel 2.0.33, 128m mem, EIDE based ( bah ) disks. there is very little
going on while executing these queries since this is a development box.
there are keys/indexes on hosts.client (primary), doms.client (primary),
types.batch & types.client (unique index), and types.type (primary). all
the 'client' columns are int4, types.batch is "char(8) not null". the
pgsql schema and mysql schema are the same and the indexes/keys are the
same.
doms has about 80 rows as does hosts. types has ~350 rows, codes has 157k
rows ( a "select count(*) from codes" takes about 3 seconds to return;
MySQL returns _immediately_ ).
am i missing something or is pgsql really that much slower ? if you need
the table layout and some sample data i can supply that...
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
[[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
selector:@selector(disableUserAccount:) name:@"UserIsWhining" object:aLuser];