On Thu, 17 Sep 1998, Howie wrote:
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.
> 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 ]---
> doms.dom, types.batch, types.active, codes.code
> hosts.client=doms.client AND
> doms.client=types.client AND
> types.batch='FREECODE' AND
> types.type=codes.type AND
> ---[ 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
> 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];
----------- 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
In response to
pgsql-general by date
|Next:||From: Greg Youngblood||Date: 1998-09-18 13:51:10|
|Subject: Help needed with performance tuning Postgres|
|Previous:||From: David Hartwig||Date: 1998-09-18 13:34:30|
|Subject: Re: [GENERAL] ORDER BY, LIKE !!? (* - new information)|