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@toodarkpark.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@q8.nrnet.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