Re: Postgres not using array

[André Volpato <andre.volpato@xxxxxxxxxxxxxxxxxxxxx>]

Gregory Stark escreveu:

André Volpato <andre.volpato@xxxxxxxxxxxxxxxxxxxxx> writes:

I think we almost reached the tuning limit, without changing the schema.

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.

Thanks Greg, I rewrote the query with a explicit join, removing the function.

The planner uses a nestloop, becouse its only a few rows, none in the end.
(A HashAggregate is used to join the same query, running against a bigger database)

The good side about the function is the facility to write in a dinamic application.
We´re gonna change it and save some bucks...

Its an impressive win, look:

 HashAggregate  (cost=19773.60..19773.61 rows=1 width=160) (actual time=0.511..0.511 rows=0 loops=1)
   ->  Nested Loop  (cost=19143.21..19773.58 rows=1 width=160) (actual time=0.509..0.509 rows=0 loops=1)
         Join Filter: ((b.benef_cod_arquivo)::text = (internacoes.cod_benef)::text)
         ->  Bitmap Heap Scan on internacoes  (cost=13.34..516.70 rows=1 width=8) (actual time=0.507..0.507 rows=0 loops=1)
               Recheck Cond: ((((ano * 100) + mes) >= 200805) AND (((ano * 100) + mes) <= 200806))
               Filter: (tipo_internacao = 'P'::bpchar)
               ->  Bitmap Index Scan on iinternacoes4  (cost=0.00..13.34 rows=708 width=0) (actual time=0.143..0.143 rows=708 loops=1)
                     Index Cond: ((((ano * 100) + mes) >= 200805) AND (((ano * 100) + mes) <= 200806))
         ->  Limit  (cost=19129.87..19209.26 rows=2117 width=48) (never executed)
               ->  HashAggregate  (cost=19129.87..19209.26 rows=2117 width=48) (never executed)
                     ->  Bitmap Heap Scan on bds_beneficiario b  (cost=822.41..18009.61 rows=56013 width=48) (never executed)
                           Recheck Cond: ((benef_referencia >= 200805) AND (benef_referencia <= 200806))
                           ->  Bitmap Index Scan on ibds_beneficiario2  (cost=0.00..808.41 rows=56013 width=0) (never executed)
                                 Index Cond: ((benef_referencia >= 200805) AND (benef_referencia <= 200806))
 Total runtime: 0.642 ms

-- 

[]´s, ACV