Re: Postgres not using array

<tt>Gregory Stark escreveu:</tt><blockquote cite="mid:87myj5jqc5.fsf@oxford.xeocode.com" type="cite"><pre
wrap=""><tt>AndréVolpato <a class="moz-txt-link-rfc2396E"
href="mailto:andre.volpato@ecomtecnologia.com.br"><andre.volpato@ecomtecnologia.com.br></a>writes:
 
</tt></pre><blockquote type="cite"><tt><br /></tt> <pre wrap=""><tt>I think we almost reached the tuning limit, without
changingthe 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
rewrotethe query with a explicit join, removing the function.<br /><br /> The planner uses a nestloop, becouse its only
afew 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´regonna 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 />    -> 
NestedLoop  (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=708width=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=2117width=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.61rows=56013 width=48) (never executed)<br />                            Recheck Cond:
((benef_referencia>= 200805) AND (benef_referencia <= 200806))<br />                            ->  Bitmap
IndexScan 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 />
 Totalruntime: 0.642 ms<br /><br /><br /><br /></tt> <pre class="moz-signature" cols="72"><tt>-- 
 

[]´s, ACV</tt></pre>