<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Gregory Stark escreveu:</tt>
<blockquote cite="mid:87myj5jqc5(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap=""><tt>André Volpato <a class="moz-txt-link-rfc2396E" href="mailto:andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br"><andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br></a> writes:
</tt></pre>
<blockquote type="cite"><tt><br>
</tt>
<pre wrap=""><tt>I think we almost reached the tuning limit, without changing the schema.
</tt></pre>
</blockquote>
<pre wrap=""><!----><tt>
It's hard to tell from the plan you posted (and with only a brief look) but it
looks to me like your query with that function is basically doing a join but
because the inner side of the join is in your function's index lookup it's
effectively forcing the use of a "nested loop" join. That's usually a good
choice for small queries against big tables but if you're joining a lot of
data there are other join types which are much faster. You might find the
planner can do a better job if you write your query as a plain SQL query and
let the optimizer figure out the best way instead of forcing its hand.</tt></pre>
</blockquote>
<tt><br>
Thanks Greg, I rewrote the query with a explicit join, removing the
function.<br>
<br>
The planner uses a nestloop, becouse its only a few rows, none in the
end.<br>
(A HashAggregate is used to join the same query, running against a
bigger database)<br>
<br>
</tt><tt>The good side about the function is the facility to write in a
dinamic application. <br>
We´re gonna change it and save some bucks...<br>
<br>
</tt><tt>Its an impressive win, look:<br>
<br>
HashAggregate (cost=19773.60..19773.61 rows=1 width=160) (actual
time=0.511..0.511 rows=0 loops=1)<br>
-> Nested Loop (cost=19143.21..19773.58 rows=1 width=160)
(actual time=0.509..0.509 rows=0 loops=1)<br>
Join Filter: ((b.benef_cod_arquivo)::text =
(internacoes.cod_benef)::text)<br>
-> Bitmap Heap Scan on internacoes (cost=13.34..516.70
rows=1 width=8) (actual time=0.507..0.507 rows=0 loops=1)<br>
Recheck Cond: ((((ano * 100) + mes) >= 200805) AND
(((ano * 100) + mes) <= 200806))<br>
Filter: (tipo_internacao = 'P'::bpchar)<br>
-> Bitmap Index Scan on iinternacoes4
(cost=0.00..13.34 rows=708 width=0) (actual time=0.143..0.143 rows=708
loops=1)<br>
Index Cond: ((((ano * 100) + mes) >= 200805)
AND (((ano * 100) + mes) <= 200806))<br>
-> Limit (cost=19129.87..19209.26 rows=2117 width=48)
(never executed)<br>
-> HashAggregate (cost=19129.87..19209.26 rows=2117
width=48) (never executed)<br>
-> Bitmap Heap Scan on bds_beneficiario b
(cost=822.41..18009.61 rows=56013 width=48) (never executed)<br>
Recheck Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))<br>
-> Bitmap Index Scan on
ibds_beneficiario2 (cost=0.00..808.41 rows=56013 width=0) (never
executed)<br>
Index Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))<br>
Total runtime: 0.642 ms<br>
<br>
<br>
<br>
</tt>
<pre class="moz-signature" cols="72"><tt>--
[]´s, ACV</tt></pre>
</body>
</html>