pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: André Volpato <andre.volpato@ecomtecnologia.com.br>
To: pgsql-performance@postgresql.org
Cc: Gregory Stark <stark@enterprisedb.com>
Subject: Re: Postgres not using array
Date: Fri, 22 Aug 2008 14:05:32 -0300
Message-ID: <48AEF1DC.6000406@ecomtecnologia.com.br> (raw )
In-Reply-To: <87myj5jqc5.fsf@oxford.xeocode.com >
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 >
<!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é 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 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>
view thread (12+ messages) latest in thread
Message-ID: <48AEF1DC.6000406@ecomtecnologia.com.br>
Permalink: ../48AEF1DC.6000406@ecomtecnologia.com.br/
Also on: postgresql.org/message-id/48AEF1DC.6000406@ecomtecnologia.com.br
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andre.volpato@ecomtecnologia.com.br, stark@enterprisedb.com
Subject: Re: Postgres not using array
In-Reply-To: <48AEF1DC.6000406@ecomtecnologia.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox