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.seylan@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
