Thread
-
slow queries
Howie <caffeine@toodarkpark.org> — 1998-09-18T03:23:41Z
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]; -
Re: [GENERAL] slow queries
David Hartwig <daveh@insightdist.com> — 1998-09-18T12:24:11Z
The postgres query sounds slower than I would expect. Have you run a "VACUUM ANALYZE"? This is needed initially, and occasionally, to compute statistics for optimal query planning. VACUUM also recovers disk space. "man vacuum" You may want to prefix your SELECT statement with EXPLAIN to display the query plan. Try this before and after the VACUUM. As far as the count(*) goes, I am not familiar with MySQL's implementation, but postgres does a sequential scan on a count(*) without a WHERE condition. Some SQL engines have this value saved off for just this kind of query. This is just one of the many performance/spaces tradeoffs. Postgres, you will find, has many other redeeming features. Howie wrote: > 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];
-
Re: [GENERAL] slow queries
Thomas Good <tomg@nrnet.org> — 1998-09-18T13:40:34Z
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 -
Re: [GENERAL] slow queries
David Hartwig <daveh@insightdist.com> — 1998-09-18T14:37:49Z
Thomas Good wrote: > 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. I would like to see it! I was not aware that table aliasing could have any impact on performance.
-
Re: [GENERAL] slow queries
Thomas Good <tomg@admin.nrnet.org> — 1998-09-18T16:27:00Z
On Fri, 18 Sep 1998, David Hartwig wrote: > I would like to see it! I was not aware that table aliasing could > have any impact on performance. Hello Oliver and David, I was a bit amazed myself. Federico Passaro, on the SQL list, helped me out some time ago when a query was failing. His code worked so well that I filed it away for a rainy day... this week I decided to try it and see if it helped hasten my slooooowest query. It did. Here is my code (hope it's coherent, I didn't name the tables!): #!/bin/sh tput clear echo -ne "Enter Unit Code: " read unit psql millie <<EOF | less SELECT unit_lname FROM crund1 WHERE unit_id = ${unit}; SELECT S.tr_id, T.i_date, S.client_lname, S.client_fname, S.eth_nic FROM svcrd1 S, trhist T WHERE S.client_id = T.pt_id AND T.unit_id = ${unit} AND T.o_date IS NULL ORDER BY S.client_lname; EOF Cheers, Tom ---------- Sisters of Charity Medical Center ---------- Department of Psychiatry ---- Thomas Good <tomg@q8.nrnet.org> Coordinator, North Richmond C.M.H.C. Information Systems 75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528 Staten Island, NY 10304 Fax: 718-354-5056 -
Re: [GENERAL] slow queries
Howie <caffeine@toodarkpark.org> — 1998-09-25T05:53:29Z
On Fri, 18 Sep 1998, Thomas Good wrote: > On Fri, 18 Sep 1998, David Hartwig wrote: > > > I would like to see it! I was not aware that table aliasing could > > have any impact on performance. > > [SNIP] > I was a bit amazed myself. Federico Passaro, on the SQL list, > helped me out some time ago when a query was failing. > His code worked so well that I filed it away for a rainy day... > this week I decided to try it and see if it helped hasten my > slooooowest query. It did. > [SNIP] seems that by creating a view ( with the query i mentioned before ), my queries were sped up by roughly 10 seconds... odd odd odd. --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org [[NSNotificationCenter defaultCenter] addObserver:systemAdministrator selector:@selector(disableUserAccount:) name:@"UserIsWhining" object:aLuser];