Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 897FC64FCCF
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 22 Aug 2008 14:06:04 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 59790-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 22 Aug 2008 14:05:46 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.ecomtecnologia.com.br (200.146.84.186.static.gvt.net.br
	[200.146.84.186])
	by postgresql.org (Postfix) with ESMTP id E09D964FD35
	for <pgsql-performance@postgresql.org>;
	Fri, 22 Aug 2008 14:05:46 -0300 (ADT)
Received: from [192.168.1.35] (unknown [192.168.1.35])
	by mail.ecomtecnologia.com.br (Postfix) with ESMTP id 4BA4979C0B5;
	Fri, 22 Aug 2008 14:05:41 -0300 (BRT)
Message-ID: <48AEF1DC.6000406@ecomtecnologia.com.br>
Date: Fri, 22 Aug 2008 14:05:32 -0300
From: =?ISO-8859-1?Q?Andr=E9_Volpato?=
 <andre.volpato@ecomtecnologia.com.br>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
CC: Gregory Stark <stark@enterprisedb.com>
Subject: Re: Postgres not using array
References: <48AC8292.5000708@ecomtecnologia.com.br>
	<48AD735D.6040403@ecomtecnologia.com.br>	<23465.1219328880@sss.pgh.pa.us>
	<48AD80FA.30100@ecomtecnologia.com.br>	<29595.1219345818@sss.pgh.pa.us>
	<48ADCA78.2090605@ecomtecnologia.com.br>
	<87myj5jqc5.fsf@oxford.xeocode.com>
In-Reply-To: <87myj5jqc5.fsf@oxford.xeocode.com>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.673 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672
X-Spam-Level: *
X-Archive-Number: 200808/283
X-Sequence-Number: 31164

<!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.fsf@oxford.xeocode.com" type="cite">
  <pre wrap=""><tt>Andr&eacute; Volpato <a class="moz-txt-link-rfc2396E" href="mailto:andre.volpato@ecomtecnologia.com.br">&lt;andre.volpato@ecomtecnologia.com.br&gt;</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&acute;re gonna change it and save some bucks...<br>
<br>
</tt><tt>Its an impressive win, look:<br>
<br>
&nbsp;HashAggregate&nbsp; (cost=19773.60..19773.61 rows=1 width=160) (actual
time=0.511..0.511 rows=0 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=19143.21..19773.58 rows=1 width=160)
(actual time=0.509..0.509 rows=0 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Join Filter: ((b.benef_cod_arquivo)::text =
(internacoes.cod_benef)::text)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on internacoes&nbsp; (cost=13.34..516.70
rows=1 width=8) (actual time=0.507..0.507 rows=0 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: ((((ano * 100) + mes) &gt;= 200805) AND
(((ano * 100) + mes) &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (tipo_internacao = 'P'::bpchar)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on iinternacoes4&nbsp;
(cost=0.00..13.34 rows=708 width=0) (actual time=0.143..0.143 rows=708
loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((((ano * 100) + mes) &gt;= 200805)
AND (((ano * 100) + mes) &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Limit&nbsp; (cost=19129.87..19209.26 rows=2117 width=48)
(never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; HashAggregate&nbsp; (cost=19129.87..19209.26 rows=2117
width=48) (never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on bds_beneficiario b&nbsp;
(cost=822.41..18009.61 rows=56013 width=48) (never executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: ((benef_referencia &gt;=
200805) AND (benef_referencia &lt;= 200806))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on
ibds_beneficiario2&nbsp; (cost=0.00..808.41 rows=56013 width=0) (never
executed)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((benef_referencia &gt;=
200805) AND (benef_referencia &lt;= 200806))<br>
&nbsp;Total runtime: 0.642 ms<br>
<br>
<br>
<br>
</tt>
<pre class="moz-signature" cols="72"><tt>-- 

[]&acute;s, ACV</tt></pre>
</body>
</html>