Trouble: subquery doesn't terminate - Mailing list 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