Mysterious performance of query because of plsql function in where condition

[Peter Alberer]

Hi there,

i have a problem with a query that uses the result of a plsql function
In the where clause:

SELECT
assignments.ass ignment_id,
assignments.pac kage_id AS package_id,
assignments.tit le AS title,
COUNT(*) AS Count
FROM
assignments INNER JOIN submissions ON
(assignments.as signment_id=sub missions.assign ment_id)
WHERE
package_id=9495 89 AND
submission_stat us(submissions. submission_id)= 'closed'
GROUP BY
assignments.ass ignment_id, assignments.pac kage_id, assignments.tit le
ORDER BY
assignments.tit le;

Postgres seems to execute the function "submission_sta tus" 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=9495 89 is used.

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------
Sort (cost=41.21..41 .21 rows=1 width=35) (actual
time=4276.978.. 4276.978 rows=0 loops=1)
Sort Key: assignments.tit le
-> HashAggregate (cost=41.19..41 .20 rows=1 width=35) (actual
time=4276.970.. 4276.970 rows=0 loops=1)
-> Hash Join (cost=2.40..41. 18 rows=1 width=35) (actual
time=4276.966.. 4276.966 rows=0 loops=1)
Hash Cond: ("outer".assign ment_id =
"inner".assignm ent_id)
-> Seq Scan on submissions (cost=0.00..38. 73 rows=9
width=4) (actual time=10.902..42 76.745 rows=38 loops=1)
Filter: (submission_sta tus(submission_ id) =
'closed'::text)
-> Hash (cost=2.40..2.4 0 rows=2 width=35) (actual
time=0.058..0.0 58 rows=0 loops=1)
-> Seq Scan on assignments (cost=0.00..2.4 0
rows=2 width=35) (actual time=0.015..0.0 52 rows=2 loops=1)
Filter: (package_id = 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_i d=s.assignment_ id)
WHERE
a.package_id=94 9589
GROUP BY
a.assignment_id , a.package_id, a.title, s.submission_id
) t1
where
submission_stat us(t1.submissio n_id)='closed'
order by
title;

QUERY PLAN

------------------------------------------------------------------------
--------------------------------------------------------------
Sort (cost=41.21..41 .22 rows=1 width=188) (actual
time=4114.251.. 4114.251 rows=0 loops=1)
Sort Key: title
-> Subquery Scan t1 (cost=41.20..41 .20 rows=1 width=188) (actual
time=4114.242.. 4114.242 rows=0 loops=1)
-> HashAggregate (cost=41.20..41 .20 rows=1 width=39) (actual
time=4114.238.. 4114.238 rows=0 loops=1)
-> Hash Join (cost=2.40..41. 18 rows=1 width=39) (actual
time=4114.235.. 4114.235 rows=0 loops=1)
Hash Cond: ("outer".assign ment_id =
"inner".assignm ent_id)
-> Seq Scan on submissions s (cost=0.00..38. 73
rows=9 width=8) (actual time=7.179..411 3.984 rows=38 loops=1)
Filter: (submission_sta tus(submission_ id) =
'closed'::text)
-> Hash (cost=2.40..2.4 0 rows=2 width=35) (actual
time=0.100..0.1 00 rows=0 loops=1)
-> Seq Scan on assignments a
(cost=0.00..2.4 0 rows=2 width=35) (actual time=0.045..0.0 94 rows=2
loops=1)
Filter: (package_id = 949589)
Total runtime: 4114.356 ms
(12 rows)

The function is nevertheless executed for every row in the submissions
table. A simple "select *, submission_stat us(submission_i d) 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

--
pe***********@w u-wien.ac.at Tel: [phone]
Abteilung für Wirtschaftsinfo rmatik, Wirtschaftsuniv ersitaet Wien,
Austria

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[mike g]

Do you have any indexes created on the submissions table? If not
postgresql has no choice but read every row in the table.

If you do they are not being used. The tuning masters would really need
to see definition of the table if the indexes are not being used.

You might find a lot of pointers in the pgsql-performance mailing list
instead of this one.

Mike
On Thu, 2004-07-01 at 06:52, Peter Alberer wrote:

Hi there,

i have a problem with a query that uses the result of a plsql function
In the where clause:

SELECT
assignments.ass ignment_id,
assignments.pac kage_id AS package_id,
assignments.tit le AS title,
COUNT(*) AS Count
FROM
assignments INNER JOIN submissions ON
(assignments.as signment_id=sub missions.assign ment_id)
WHERE
package_id=9495 89 AND
submission_stat us(submissions. submission_id)= 'closed'
GROUP BY
assignments.ass ignment_id, assignments.pac kage_id, assignments.tit le
ORDER BY
assignments.tit le;

Postgres seems to execute the function "submission_sta tus" 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=9495 89 is used.

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------
Sort (cost=41.21..41 .21 rows=1 width=35) (actual
time=4276.978.. 4276.978 rows=0 loops=1)
Sort Key: assignments.tit le
-> HashAggregate (cost=41.19..41 .20 rows=1 width=35) (actual
time=4276.970.. 4276.970 rows=0 loops=1)
-> Hash Join (cost=2.40..41. 18 rows=1 width=35) (actual
time=4276.966.. 4276.966 rows=0 loops=1)
Hash Cond: ("outer".assign ment_id =
"inner".assignm ent_id)
-> Seq Scan on submissions (cost=0.00..38. 73 rows=9
width=4) (actual time=10.902..42 76.745 rows=38 loops=1)
Filter: (submission_sta tus(submission_ id) =
'closed'::text)
-> Hash (cost=2.40..2.4 0 rows=2 width=35) (actual
time=0.058..0.0 58 rows=0 loops=1)
-> Seq Scan on assignments (cost=0.00..2.4 0
rows=2 width=35) (actual time=0.015..0.0 52 rows=2 loops=1)
Filter: (package_id = 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_i d=s.assignment_ id)
WHERE
a.package_id=94 9589
GROUP BY
a.assignment_id , a.package_id, a.title, s.submission_id
) t1
where
submission_stat us(t1.submissio n_id)='closed'
order by
title;

QUERY PLAN

------------------------------------------------------------------------
--------------------------------------------------------------
Sort (cost=41.21..41 .22 rows=1 width=188) (actual
time=4114.251.. 4114.251 rows=0 loops=1)
Sort Key: title
-> Subquery Scan t1 (cost=41.20..41 .20 rows=1 width=188) (actual
time=4114.242.. 4114.242 rows=0 loops=1)
-> HashAggregate (cost=41.20..41 .20 rows=1 width=39) (actual
time=4114.238.. 4114.238 rows=0 loops=1)
-> Hash Join (cost=2.40..41. 18 rows=1 width=39) (actual
time=4114.235.. 4114.235 rows=0 loops=1)
Hash Cond: ("outer".assign ment_id =
"inner".assignm ent_id)
-> Seq Scan on submissions s (cost=0.00..38. 73
rows=9 width=8) (actual time=7.179..411 3.984 rows=38 loops=1)
Filter: (submission_sta tus(submission_ id) =
'closed'::text)
-> Hash (cost=2.40..2.4 0 rows=2 width=35) (actual
time=0.100..0.1 00 rows=0 loops=1)
-> Seq Scan on assignments a
(cost=0.00..2.4 0 rows=2 width=35) (actual time=0.045..0.0 94 rows=2
loops=1)
Filter: (package_id = 949589)
Total runtime: 4114.356 ms
(12 rows)

The function is nevertheless executed for every row in the submissions
table. A simple "select *, submission_stat us(submission_i d) 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

--
pe***********@w u-wien.ac.at Tel: [phone]
Abteilung für Wirtschaftsinfo rmatik, Wirtschaftsuniv ersitaet Wien,
Austria

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match