Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] slow queries

From: Thomas Good <tomg(at)nrnet(dot)org>
To: Howie <caffeine(at)toodarkpark(dot)org>
Cc: pgsql-general(at)postgreSQL(dot)org
Subject: Re: [GENERAL] slow queries
Date: 1998-09-18 13:40:34
Message-ID: Pine.LNX.3.96.980918093822.4320A-100000@mailhost.nrnet.org (view raw or flat)
Thread:
Lists: pgsql-general
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 


In response to

Responses

pgsql-general by date

Next:From: Greg YoungbloodDate: 1998-09-18 13:51:10
Subject: Help needed with performance tuning Postgres
Previous:From: David HartwigDate: 1998-09-18 13:34:30
Subject: Re: [GENERAL] ORDER BY, LIKE !!? (* - new information)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group