Thread: slow queries
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];
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];
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
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.
Thomas Good 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. Please do, to the list. I have never heard of 'table aliasing' and would like to know more. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to in return." Luke 6:38
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
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];