Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C757A64FD1E for ; Thu, 21 Aug 2008 11:51:58 -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 26364-10 for ; Thu, 21 Aug 2008 11:51:43 -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 3DE6564FD12 for ; Thu, 21 Aug 2008 11:51:47 -0300 (ADT) Received: from [192.168.1.35] (unknown [192.168.1.35]) by mail.ecomtecnologia.com.br (Postfix) with ESMTP id D5D8279C0AB for ; Thu, 21 Aug 2008 11:51:44 -0300 (BRT) Message-ID: <48AD80FA.30100@ecomtecnologia.com.br> Date: Thu, 21 Aug 2008 11:51:38 -0300 From: =?ISO-8859-1?Q?Andr=E9_Volpato?= User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Postgres not using array References: <48AC8292.5000708@ecomtecnologia.com.br> <48AD735D.6040403@ecomtecnologia.com.br> <23465.1219328880@sss.pgh.pa.us> In-Reply-To: <23465.1219328880@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/251 X-Sequence-Number: 31132 Tom Lane escreveu: > =3D?ISO-8859-1?Q?Andr=3DE9_Volpato?=3D writes: > =20 >> Explain output: >> HashAggregate (cost=3D19826.23..19826.96 rows=3D73 width=3D160) (act= ual=20 >> time=3D11826.754..11826.754 rows=3D0 loops=3D1) >> -> Subquery Scan b2 (cost=3D19167.71..19817.21 rows=3D722 width=3D= 160)=20 >> (actual time=3D11826.752..11826.752 rows=3D0 loops=3D1) >> Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,=20 >> 'qtdI'::text, 'P'::bpchar) >=3D 1::numeric) >> -> Limit (cost=3D19167.71..19248.89 rows=3D2165 width=3D48)= (actual=20 >> time=3D415.157..621.043 rows=3D28923 loops=3D1) >> =20 > > So I guess the question is "what is the bds_internacoes function, and > why is it so slow?"=09 This function is quite fast: Aggregate (cost=3D5.17..5.18 rows=3D1 width=3D12) (actual time=3D0.286.= =2E0.287=20 rows=3D1 loops=3D1) -> Index Scan using iinternacoes4 on internacoes (cost=3D0.01..5.16 = rows=3D1 width=3D12) (actual time=3D0.273..0.273 rows=3D0 loops=3D1) Index Cond: ((((ano * 100) + mes) >=3D 200801) AND (((ano * 100)= =20 + mes) <=3D 200806) AND ((cod_benef)::text =3D '0005375200'::text)) Filter: (tipo_internacao =3D 'P'::bpchar) Total runtime: 0.343 ms The problem is that its fired up against 29K rows, wich takes the total runtime about 10s. We are guessing that a dual core 3.0GHz will beat up a quad core 2.2, at least in this environmnent with less than 4 concurrent queryes. --=20 []=B4s, ACV