Rainer Pruy wrote:
> Thanks for the hint.
> I should have been considering that in the first place.
> (But the obvious is easily left unrecognised..)
>
> The prepared statement gives:
>
> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..25.18 rows=2 width=175) (actual time=36.116..49.998 rows=1 loops=1)
> -> Index Scan using x_context_01 on context c (cost=0.00..10.76 rows=2 width=67) (actual time=0.029..6.947
rows=12706loops=1)
> Index Cond: ((contextid)::text = $1)
> -> Index Scan using x_fk_context_hierarchy_02 on context_hierarchy h (cost=0.00..7.20 rows=1 width=108) (actual
time=0.003..0.003
> rows=0 loops=12706)
> Index Cond: (h.contextidx = c.idx)
> Filter: (((h.hierarchyname)::text = $2) AND (h.parentidx = $3))
> Total runtime: 50.064 ms
> (7 rows)
>
>
> And that is quite a bad plan given the current distribution of values.
>
Another approach might be to rewrite recursion into your hierarchy with
the in 8.4 new WITH RECURSIVE option in sql queries. The possible gains
there are way beyond anything you can accomplish with optimizing
recursive functions.
Regards,
Yeb Havinga