X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 27AD0D1B27C for ; Fri, 2 Jul 2004 04:47:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61543-03 for ; Fri, 2 Jul 2004 07:47:04 +0000 (GMT) Received: from obelix.wu-wien.ac.at (obelix.wu-wien.ac.at [137.208.3.41]) by svr1.postgresql.org (Postfix) with ESMTP id 4028CD1B1CE for ; Fri, 2 Jul 2004 04:46:57 -0300 (ADT) Received: from ekelhardt (floppy.wu-wien.ac.at [137.208.224.91]) by obelix.wu-wien.ac.at (8.12.8/8.12.6) with ESMTP id i627krSU289624 for ; Fri, 2 Jul 2004 09:46:54 +0200 (envelope-from h9351252@obelix.wu-wien.ac.at) From: "Peter Alberer" To: Subject: Mysterious performance of query because of plsql function in where condition Date: Fri, 2 Jul 2004 09:48:48 +0200 Message-ID: <000001c46009$062e8260$5be0d089@ekelhardt> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-WU-uvscan-status: clean v4.3.20/v4371 hiphop c96b3491ee14de741e972cbba84a0d0d X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200407/5 X-Sequence-Number: 7396 Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id, assignments.title AS title, COUNT(*) AS Count FROM assignments INNER JOIN submissions ON (assignments.assignment_id=3Dsubmissions.assignment_id) WHERE package_id=3D949589 AND submission_status(submissions.submission_id)=3D'closed' GROUP BY assignments.assignment_id, assignments.package_id, assignments.title ORDER BY assignments.title; Postgres seems to execute the function "submission_status" for every row of the submissions table (~1500 rows). The query therefore takes quite a lot time, although in fact no row is returned from the assignments table when the condition package_id=3D949589 is used. QUERY PLAN ------------------------------------------------------------------------ --- --------------------------------------------------- Sort (cost=3D41.21..41.21 rows=3D1 width=3D35) (actual time=3D4276.978..4276.978 rows=3D0 loops=3D1) Sort Key: assignments.title -> HashAggregate (cost=3D41.19..41.20 rows=3D1 width=3D35) (actual time=3D4276.970..4276.970 rows=3D0 loops=3D1) -> Hash Join (cost=3D2.40..41.18 rows=3D1 width=3D35) (actual time=3D4276.966..4276.966 rows=3D0 loops=3D1) Hash Cond: ("outer".assignment_id =3D "inner".assignment_id) -> Seq Scan on submissions (cost=3D0.00..38.73 rows=3D9 width=3D4) (actual time=3D10.902..4276.745 rows=3D38 loops=3D1) Filter: (submission_status(submission_id) =3D 'closed'::text) -> Hash (cost=3D2.40..2.40 rows=3D2 width=3D35) (actual time=3D0.058..0.058 rows=3D0 loops=3D1) -> Seq Scan on assignments (cost=3D0.00..2.40 rows=3D2 width=3D35) (actual time=3D0.015..0.052 rows=3D2 loops=3D1) Filter: (package_id =3D 949589) Total runtime: 4277.078 ms (11 rows) I therefore tried to rephrase the query, to make sure that the function is only used for the rows returned by the join but not even the following does help (the subselect t1 does not return a single row): select * from ( SELECT a.assignment_id, a.package_id, a.title, s.submission_id, COUNT(*) AS Count FROM assignments a INNER JOIN submissions s ON (a.assignment_id=3Ds.assignment_id) WHERE a.package_id=3D949589 GROUP BY a.assignment_id, a.package_id, a.title, s.submission_id ) t1 where submission_status(t1.submission_id)=3D'closed' order by title; QUERY PLAN ------------------------------------------------------------------------ --- ----------------------------------------------------------- Sort (cost=3D41.21..41.22 rows=3D1 width=3D188) (actual time=3D4114.251..4114.251 rows=3D0 loops=3D1) Sort Key: title -> Subquery Scan t1 (cost=3D41.20..41.20 rows=3D1 width=3D188) (actual time=3D4114.242..4114.242 rows=3D0 loops=3D1) -> HashAggregate (cost=3D41.20..41.20 rows=3D1 width=3D39) (actu= al time=3D4114.238..4114.238 rows=3D0 loops=3D1) -> Hash Join (cost=3D2.40..41.18 rows=3D1 width=3D39) (act= ual time=3D4114.235..4114.235 rows=3D0 loops=3D1) Hash Cond: ("outer".assignment_id =3D "inner".assignment_id) -> Seq Scan on submissions s (cost=3D0.00..38.73 rows=3D9 width=3D8) (actual time=3D7.179..4113.984 rows=3D38 loops=3D1) Filter: (submission_status(submission_id) =3D 'closed'::text) -> Hash (cost=3D2.40..2.40 rows=3D2 width=3D35) (act= ual time=3D0.100..0.100 rows=3D0 loops=3D1) -> Seq Scan on assignments a (cost=3D0.00..2.40 rows=3D2 width=3D35) (actual time=3D0.045..0.094 rows=3D2 loops=3D1) Filter: (package_id =3D 949589) Total runtime: 4114.356 ms (12 rows) The function is nevertheless executed for every row in the submissions table. A simple "select *, submission_status(submission_id) from submissions" takes about the same time as the 2 queries stated above. The whole database has been vacuum analysed right before the explain analyse output has been captured. What can I do to reduce the time this query takes? And why is the function executed although there is no row in the result set of t1 in my rephrased query? TIA, peter Ps: table definitions: Table "public.assignments" Column | Type | Modifiers ---------------+-----------------------------+------------------------ assignment_id | integer | not null title | character varying(100) | not null max_grade | smallint | not null start_date | timestamp without time zone | not null default now() end_date | timestamp without time zone | not null over_due_date | timestamp without time zone | score_release | smallint | not null default 1 package_id | integer | not null cal_item_id | integer | Indexes: "assignments_pk" primary key, btree (assignment_id) Check constraints: "assignments_sr_ck" CHECK (score_release =3D 1 OR score_release =3D 2 OR score_release =3D 3) Foreign-key constraints: "cal_item_id" FOREIGN KEY (cal_item_id) REFERENCES cal_items(cal_item_id) ON DELETE SET NULL "package_id_fk" FOREIGN KEY (package_id) REFERENCES apm_packages(package_id) "assignment_id_fk" FOREIGN KEY (assignment_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE Table "public.submissions" Column | Type | Modifiers ---------------+-----------------------------+----------- submission_id | integer | not null person_id | integer | not null assignment_id | integer | not null last_modified | timestamp without time zone | not null recovery_date | timestamp without time zone | grading | smallint | grading_date | timestamp without time zone | Indexes: "submissions_pk" primary key, btree (submission_id) "submissions_person_ass_un" unique, btree (person_id, assignment_id) Foreign-key constraints: "assignment_id_fk" FOREIGN KEY (assignment_id) REFERENCES assignments(assignment_id) "person_id_fk" FOREIGN KEY (person_id) REFERENCES persons(person_id) -- peter.alberer@wu-wien.ac.at Tel: +43/1/31336/4341 Abteilung f=FCr Wirtschaftsinformatik, Wirtschaftsuniversitaet Wien, Austria