Lists: | pgsql-general |
---|
From: | mwilson(at)the-wire(dot)com (Mel Wilson) |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Trouble: subquery doesn't terminate |
Date: | 2000-01-25 21:41:04 |
Message-ID: | whhj4ks/KLNV089yn@the-wire.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Trouble: I'm running CGI perl routines from Apache (without
mod_perl) to query a small database. This query ran over 6 minutes
before Apache timed out and dropped the pipe:
$result = $conn->exec(qq/
SELECT t.tune_id, t.title
FROM tune t
WHERE t.tune_id IN
(SELECT c.tune_id FROM composer c WHERE c.person_id = $person_id)
/);
A similar query works when it joins tune and composer tables to
eliminate the subquery. There was no other database processing going
on at the time that I know of.
Any help on where to look for the problem gratefully received.
Mel.
================================================
Software:
[PostgreSQL 6.5.3 on i486-pc-linux-gnulibc1, compiled by gcc 2.7.2.3]
The Apache CGI code runs as user-id "nobody".
The database structure (from pg_dump) is:
CREATE TABLE "tune" (
"tune_id" int4 NOT NULL,
"title" text,
"publisher" text,
"date" datetime);
REVOKE ALL on "tune" from PUBLIC;
GRANT ALL on "tune" to "mwilson";
GRANT SELECT on "tune" to "nobody";
CREATE TABLE "person" (
"person_id" int4 NOT NULL,
"name" text,
"alias" text);
REVOKE ALL on "person" from PUBLIC;
GRANT ALL on "person" to "mwilson";
GRANT SELECT on "person" to "nobody";
CREATE TABLE "source" (
"source_id" int4 NOT NULL,
"title" text,
"type" character(2),
"publisher" text,
"date" datetime,
"serial" text,
"leader_id" int4);
REVOKE ALL on "source" from PUBLIC;
GRANT ALL on "source" to "mwilson";
GRANT SELECT on "source" to "nobody";
CREATE TABLE "occurrence" (
"ocid" int4 NOT NULL,
"source_id" int4,
"tune_id" int4);
REVOKE ALL on "occurrence" from PUBLIC;
GRANT ALL on "occurrence" to "mwilson";
GRANT SELECT on "occurrence" to "nobody";
CREATE TABLE "performance" (
"occurrence_id" int4,
"person_id" int4,
"role" text);
REVOKE ALL on "performance" from PUBLIC;
GRANT ALL on "performance" to "mwilson";
GRANT SELECT on "performance" to "nobody";
CREATE TABLE "appearance" (
"recording_id" int4,
"person_id" int4,
"role" text);
REVOKE ALL on "appearance" from PUBLIC;
GRANT ALL on "appearance" to "mwilson";
GRANT SELECT on "appearance" to "nobody";
CREATE TABLE "composer" (
"tune_id" int4,
"person_id" int4);
REVOKE ALL on "composer" from PUBLIC;
GRANT ALL on "composer" to "mwilson";
GRANT SELECT on "composer" to "nobody";
There are indexes on tune.tune_id, person.person_id, source.source_id
and occurrence.ocid .
================================================
Row counts in the database are:
Person: 1631
Source: 316
Tune: 2818
Appearance: 687
Composer: 3059
Occurrence: 3946
Performance: 12
From: | Herbert Liechti <Herbert(dot)Liechti(at)thinx(dot)ch> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Cc: | Mel Wilson <mwilson(at)the-wire(dot)com> |
Subject: | Re: [GENERAL] Trouble: subquery doesn't terminate |
Date: | 2000-01-26 07:27:25 |
Message-ID: | 388EA1DD.6685C433@thinx.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Mel Wilson wrote:
>
> Trouble: I'm running CGI perl routines from Apache (without
> mod_perl) to query a small database. This query ran over 6 minutes
> before Apache timed out and dropped the pipe:
>
> $result = $conn->exec(qq/
> SELECT t.tune_id, t.title
> FROM tune t
> WHERE t.tune_id IN
> (SELECT c.tune_id FROM composer c WHERE c.person_id = $person_id)
> /);
The IN Clause is known to be very slow. Try to use the EXISTS clause
instead. I had the same problem. After changing to the EXISTS
variant my performance troubles went away.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti E-Mail: Herbert(dot)Liechti(at)thinx(dot)ch
ThinX networked business services Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: | mwilson(at)the-wire(dot)com (Mel Wilson) |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Trouble: subquery doesn't terminate |
Date: | 2000-01-27 17:08:50 |
Message-ID: | iuHk4ks/KjXZ089yn@the-wire.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
In article <388EA1DD(dot)6685C433(at)thinx(dot)ch>,
Herbert Liechti <Herbert(dot)Liechti(at)thinx(dot)ch> wrote:
>> [ ... ] This query ran over 6 minutes
>> before Apache timed out and dropped the pipe:
>>
>> $result = $conn->exec(qq/
>> SELECT t.tune_id, t.title
>> FROM tune t
>> WHERE t.tune_id IN
>> (SELECT c.tune_id FROM composer c WHERE c.person_id = $person_id)
>> /);
>The IN Clause is known to be very slow. Try to use the EXISTS clause
>instead. I had the same problem. After changing to the EXISTS
>variant my performance troubles went away.
Thanks for your reply. I'm not sure how to use EXISTS in this case
(a list of tunes composed by a given person.) but it's a moot point
since the fully joined query
SELECT t.tune_id, t.title
FROM tune t, composer c
WHERE t.tune_id = c.tune_id
AND $person_id = c.person_id
ORDER BY t.title
runs in 2 seconds. (in today's test .. while the sub-select was taking
over 7:30 before Netscape killed it.)
Thanks again. Mel.