Re: [GENERAL] slow queries

On Thu, 17 Sep 1998, Howie wrote:

Howie,

I have a query that looks up a patient's placement (which ward)
in one table and then looks up diagnostic and demographic data
in another...takes 53 seconds. Ouch.

However, when I use table aliasing...the query output is on the
screen *before* I submit the query. ;-)

I'll send syntax, if you like.

Tom

> 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];
>
>
>
>
>
>

Cheers,
Tom

----------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good, System Administrator <tomg(at)q8(dot)nrnet(dot)org>
North Richmond CMHC/Residential Services Phone: 718-354-5528
75 Vanderbilt Ave, Quarters 8 Fax: 718-354-5056
Staten Island, NY 10304 www.panix.com/~ugd
----
Powered by PostgreSQL 6.3.2 / Perl 5.004 / DBI-0.91::DBD-PG-0.69