It is IMMUTABLE. I attach the output of EXPLAIN both with and without
the simple function (returning true only) in the query.
On 5/11/12 4:21 PM, Alban Hertroys wrote:
On 11 May 2012 15:57, Inanc Seylan<inanc.sey...@gmail.com> wrote:
Hi all,
I have implemented a user-defined function in C that returns a boolean value
after some computation. Now I have a query Q such that when I specify the
function in the WHERE clause of Q, Q runs in 40 secs and if I don't use the
function it runs in 4 secs. Then I thought that my implementation of this
function could be slow; so I decided to write a very simple function that
just returns true without any computation. To my surprise, it also takes
around 40 seconds to run Q with the new very simple function. Does anybody
have a clue about what might be going wrong?
Is that a VOLATILE, STABLE or IMMUTABLE function? What's the output of
EXPLAIN ANALYZE?
"Unique (cost=60853.14..61238.07 rows=38493 width=195)"
" -> Sort (cost=60853.14..60949.37 rows=38493 width=195)"
" Sort Key: s2.name, s1.name, s0.name"
" -> Hash Join (cost=51349.05..54235.95 rows=38493 width=195)"
" Hash Cond: (c5.individual = s0.id)"
" -> Bitmap Heap Scan on conceptassertions c5
(cost=1341.13..3997.19 rows=61525 width=4)"
" Recheck Cond: (concept = 576)"
" -> Bitmap Index Scan on conceptassertions_concept_idx
(cost=0.00..1325.75 rows=61525 width=0)"
" Index Cond: (concept = 576)"
" -> Hash (cost=50007.68..50007.68 rows=20 width=207)"
" -> Nested Loop (cost=34886.95..50007.68 rows=20
width=207)"
" Join Filter: (s1.id = c2.individual)"
" -> Nested Loop (cost=34812.31..47917.51 rows=1
width=219)"
" Join Filter: ((s0.id = r4.rhs) AND (s1.id =
r4.lhs))"
" -> Seq Scan on roleassertions r4
(cost=0.00..9976.73 rows=9342 width=8)"
" Filter: (role = 712)"
" -> Materialize (cost=34812.31..37613.82
rows=2 width=211)"
" -> Hash Join (cost=34812.31..37613.81
rows=2 width=211)"
" Hash Cond: (s2.id = c0.individual)"
" -> Seq Scan on symbols s2
(cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash (cost=34812.28..34812.28
rows=2 width=158)"
" -> Hash Join
(cost=32010.78..34812.28 rows=2 width=158)"
" Hash Cond: (s1.id =
r1.rhs)"
" -> Seq Scan on
symbols s1 (cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash
(cost=32010.76..32010.76 rows=2 width=89)"
" -> Hash Join
(cost=29209.26..32010.76 rows=2 width=89)"
" Hash Cond:
(s0.id = r3.rhs)"
" -> Seq
Scan on symbols s0 (cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash
(cost=29209.23..29209.23 rows=2 width=20)"
" ->
Nested Loop (cost=16263.05..29209.23 rows=2 width=20)"
"
Join Filter: (fake_filter(r3.rhs, r1.rhs, c0.individual) AND (c0.individual =
r3.lhs))"
"
-> Merge Join (cost=16263.05..16264.49 rows=11 width=12)"
"
Merge Cond: (c0.individual = r1.lhs)"
"
-> Sort (cost=3497.01..3497.52 rows=205 width=4)"
"
Sort Key: c0.individual"
"
-> Bitmap Heap Scan on conceptassertions c0
(cost=884.09..3489.14 rows=205 width=4)"
"
Recheck Cond: (concept = 352)"
"
Filter: ((individual & 4) = 4)"
"
-> Bitmap Index Scan on conceptassertions_concept_idx
(cost=0.00..884.04 rows=41031 width=0)"
"
Index Cond: (concept = 352)"
"
-> Sort (cost=12765.86..12766.10 rows=98 width=8)"
"
Sort Key: r1.lhs"
"
-> Seq Scan on roleassertions r1 (cost=0.00..12762.62 rows=98
width=8)"
"
Filter: ((role = 696) AND ((rhs & 4) = 4))"
"
-> Materialize (cost=0.00..12767.29 rows=934 width=8)"
"
-> Seq Scan on roleassertions r3 (cost=0.00..12762.62 rows=934
width=8)"
"
Filter: ((role = 616) AND ((rhs & 4) = 4))"
" -> Bitmap Heap Scan on conceptassertions c2
(cost=74.64..2047.70 rows=3397 width=4)"
" Recheck Cond: (concept = 192)"
" -> Bitmap Index Scan on
conceptassertions_concept_idx (cost=0.00..73.79 rows=3397 width=0)"
" Index Cond: (concept = 192)"
"Unique (cost=85366.73..86521.52 rows=115479 width=195)"
" -> Sort (cost=85366.73..85655.42 rows=115479 width=195)"
" Sort Key: s2.name, s1.name, s0.name"
" -> Hash Join (cost=50663.38..53550.52 rows=115479 width=195)"
" Hash Cond: (c5.individual = s0.id)"
" -> Bitmap Heap Scan on conceptassertions c5
(cost=1341.13..3997.19 rows=61525 width=4)"
" Recheck Cond: (concept = 576)"
" -> Bitmap Index Scan on conceptassertions_concept_idx
(cost=0.00..1325.75 rows=61525 width=0)"
" Index Cond: (concept = 576)"
" -> Hash (cost=49321.50..49321.50 rows=60 width=207)"
" -> Nested Loop (cost=44632.52..49321.50 rows=60
width=207)"
" Join Filter: (s1.id = c2.individual)"
" -> Hash Join (cost=44557.88..47231.33 rows=1
width=219)"
" Hash Cond: (s2.id = c0.individual)"
" -> Seq Scan on symbols s2
(cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash (cost=44557.87..44557.87 rows=1
width=166)"
" -> Hash Join (cost=41884.42..44557.87
rows=1 width=166)"
" Hash Cond: (s1.id = r1.rhs)"
" -> Seq Scan on symbols s1
(cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash (cost=41884.41..41884.41
rows=1 width=97)"
" -> Hash Join
(cost=39210.96..41884.41 rows=1 width=97)"
" Hash Cond: (s0.id =
r3.rhs)"
" -> Seq Scan on
symbols s0 (cost=0.00..2289.32 rows=102432 width=69)"
" -> Hash
(cost=39210.94..39210.94 rows=1 width=28)"
" -> Merge Join
(cost=39203.58..39210.94 rows=1 width=28)"
" Merge
Cond: ((r4.rhs = r3.rhs) AND (c0.individual = r3.lhs))"
" -> Sort
(cost=26394.89..26395.00 rows=46 width=20)"
" Sort
Key: r4.rhs, c0.individual"
" ->
Hash Join (cost=16264.63..26393.62 rows=46 width=20)"
"
Hash Cond: (r4.lhs = r1.rhs)"
"
-> Seq Scan on roleassertions r4 (cost=0.00..9976.73 rows=9342 width=8)"
"
Filter: (role = 712)"
"
-> Hash (cost=16264.49..16264.49 rows=11 width=12)"
"
-> Merge Join (cost=16263.05..16264.49 rows=11 width=12)"
"
Merge Cond: (c0.individual = r1.lhs)"
"
-> Sort (cost=3497.01..3497.52 rows=205 width=4)"
"
Sort Key: c0.individual"
"
-> Bitmap Heap Scan on conceptassertions c0
(cost=884.09..3489.14 rows=205 width=4)"
"
Recheck Cond: (concept = 352)"
"
Filter: ((individual & 4) = 4)"
"
-> Bitmap Index Scan on conceptassertions_concept_idx
(cost=0.00..884.04 rows=41031 width=0)"
"
Index Cond: (concept = 352)"
"
-> Sort (cost=12765.86..12766.10 rows=98 width=8)"
"
Sort Key: r1.lhs"
"
-> Seq Scan on roleassertions r1 (cost=0.00..12762.62
rows=98 width=8)"
"
Filter: ((role = 696) AND ((rhs & 4) = 4))"
" -> Sort
(cost=12808.70..12811.03 rows=934 width=8)"
" Sort
Key: r3.rhs, r3.lhs"
" ->
Seq Scan on roleassertions r3 (cost=0.00..12762.62 rows=934 width=8)"
"
Filter: ((role = 616) AND ((rhs & 4) = 4))"
" -> Bitmap Heap Scan on conceptassertions c2
(cost=74.64..2047.70 rows=3397 width=4)"
" Recheck Cond: (concept = 192)"
" -> Bitmap Index Scan on
conceptassertions_concept_idx (cost=0.00..73.79 rows=3397 width=0)"
" Index Cond: (concept = 192)"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
