title: How to speed up a complicated query in Postgresql
body: <p>How to speed up the following query execution? it took 65s just to retrieve 37 records. Any solution guys? (I'm using PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit)
  This is the query:</p>
  
  <pre><code>select cc.claim_id, 
  cc.claim_date, 
  CONCAT(cc.user_id, ' | ', uu.user_name) as user_name,
  CONCAT(f_get_channel_id_by_territory_id(cc.territory_id), ' | ', f_get_channel_name(f_get_channel_id_by_territory_id(cc.territory_id))) AS channel_name,
  f_get_cluster(cc.user_id) AS cluster_id,
  ff.frontliner_name 
  from t_trx_card_claim cc join t_mtr_user uu on uu.user_id = cc.user_id
  left join t_mtr_outlet_frontliner ff on f_get_territory_id(ff.outlet_id) = cc.territory_id
  where f_get_cluster(cc.user_id) = '36'
  </code></pre>
  
  <p>And this is an explain analyze output (<a href="http://explain.depesz.com/s/z97" rel="nofollow">see also on explain.depesz.com</a>):</p>
  
  <pre><code>Nested Loop Left Join  (cost=0.00..83503.84 rows=646 width=47) (actual time=2000.830..65689.982 rows=37 loops=1)
    Join Filter: (f_get_territory_id(ff.outlet_id) = cc.territory_id)
    -&gt;  Nested Loop  (cost=0.00..433.50 rows=7 width=35) (actual time=174.417..198.364 rows=37 loops=1)
          -&gt;  Seq Scan on t_trx_card_claim cc  (cost=0.00..375.53 rows=7 width=21) (actual time=174.407..197.932 rows=37 loops=1)
                Filter: (f_get_cluster(user_id) = 36)
          -&gt;  Index Scan using ix_user_8 on t_mtr_user uu  (cost=0.00..8.27 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=37)
                Index Cond: ((user_id)::text = (cc.user_id)::text)
    -&gt;  Materialize  (cost=0.00..1811.51 rows=42701 width=21) (actual time=0.006..30.225 rows=42701 loops=37)
          -&gt;  Seq Scan on t_mtr_outlet_frontliner ff  (cost=0.00..1347.01 rows=42701 width=21) (actual time=0.003..27.457 rows=42701 loops=1)
  Total runtime: 65690.524 ms
  </code></pre>