slow queries - Mailing list pgsql-general

From Howie
Subject slow queries
Date
Msg-id Pine.LNX.3.96.980917144524.21157B-100000@brap-eth0.toodarkpark.org
Whole thread Raw
Responses Re: [GENERAL] slow queries
List 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@toodarkpark.org>   URL: http://www.toodarkpark.org
[[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
  selector:@selector(disableUserAccount:) name:@"UserIsWhining" object:aLuser];






pgsql-general by date:

Previous
From: Christophe Pettus
Date:
Subject: ...
Next
From: "Gene Selkov, Jr."
Date:
Subject: userguide example fixes