From: | Howie <caffeine(at)toodarkpark(dot)org> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Cc: | caffeine(at)toodarkpark(dot)org |
Subject: | slow queries |
Date: | 1998-09-18 03:23:41 |
Message-ID: | Pine.LNX.3.96.980917144524.21157B-100000@brap-eth0.toodarkpark.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
[[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
selector:@selector(disableUserAccount:) name:@"UserIsWhining" object:aLuser];
From | Date | Subject | |
---|---|---|---|
Next Message | Wim Ceulemans | 1998-09-18 06:52:23 | Re: [GENERAL] slow queries |
Previous Message | Gene Selkov, Jr. | 1998-09-18 03:08:02 | userguide example fixes |