X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 402879DD56B for ; Wed, 22 Mar 2006 08:24:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29823-05-2 for ; Wed, 22 Mar 2006 08:24:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.198]) by postgresql.org (Postfix) with ESMTP id 1BE4B9DD560 for ; Wed, 22 Mar 2006 08:24:21 -0400 (AST) Received: by zproxy.gmail.com with SMTP id n1so171923nzf for ; Wed, 22 Mar 2006 04:24:25 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=BUW6fhHinwUnltigz3vnvLPDgB8gzqbZxWgtmPF6e+cCwn1MuQjRm1MZz93+TmGhLBa2V2F3wmybcvFkglEpB4U7h9Gh21ta1WhYXIlPpEOvm44dhFCZ+e6RTbRKQ/3bXrDnZr7F6mxuzeJXj2U2mMg6xnDs2Rb5t5X5MjJbnAA= Received: by 10.37.14.68 with SMTP id r68mr997715nzi; Wed, 22 Mar 2006 04:24:23 -0800 (PST) Received: by 10.37.13.49 with HTTP; Wed, 22 Mar 2006 04:23:53 -0800 (PST) Message-ID: <8398dc6d0603220423i24ae9d41k@mail.gmail.com> Date: Wed, 22 Mar 2006 09:23:53 -0300 From: "Alejandro D. Burne" To: pgsql-performance@postgresql.org Subject: Re: Sequence Scan vs. Index scan In-Reply-To: <20060322121333.GB27640@uio.no> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_17787_25182699.1143030233867" References: <44204107.3040708@mandic.com.br> <001a01c64d14$80f4a840$8b00000a@reimer> <8398dc6d0603220350x4ee9c298h@mail.gmail.com> <20060322121333.GB27640@uio.no> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.099 required=5 tests=[AWL=0.098, HTML_MESSAGE=0.001] X-Spam-Score: 0.099 X-Spam-Level: X-Archive-Number: 200603/445 X-Sequence-Number: 17882 ------=_Part_17787_25182699.1143030233867 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 2006/3/22, Steinar H. Gunderson : > > On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote: > > Explains: > > ------------ > > With SET ENABLE_SEQSCAN TO ON; > > HashAggregate (cost=3D251306.99..251627.36 rows=3D11650 width=3D78) > > You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN. > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > Sorry, this is the result: WITH SET ENABLE_SEQSCAN TO ON; HashAggregate (cost=3D251306.99..251627.36 rows=3D11650 width=3D78) (actua= l time=3D 25089.024..25090.340 rows=3D1780 loops=3D1) -> Hash Join (cost=3D1894.30..250155.54 rows=3D153526 width=3D78) (actu= al time=3D3190.599..24944.418 rows=3D38009 loops=3D1) Hash Cond: ("outer".codigoliquidacionos =3D "inner".codigoliquidacionos) -> Seq Scan on detalleprestaciones dp=20 (cost=3D0.00..219621.32rows=3D5420932 width=3D82) (actual time=3D 0.058..23198.852 rows=3D5421786 loops=3D1) -> Hash (cost=3D1891.01..1891.01 rows=3D1318 width=3D4) (actual t= ime=3D 60.777..60.777 rows=3D1530 loops=3D1) -> Bitmap Heap Scan on liquidacionesos l (cost=3D 43.89..1891.01 rows=3D1318 width=3D4) (actual time=3D1.843..59.574 rows=3D1= 530 loops=3D1) Recheck Cond: (codigoobrasocial =3D 54) Filter: ((per_a =3D '2005'::bpchar) AND facturada) -> Bitmap Index Scan on ixliqos_os=20 (cost=3D0.00..43.89rows=3D4541 width=3D0) (actual time=3D 1.439..1.439 rows=3D4736 loops=3D1) Index Cond: (codigoobrasocial =3D 54) Total runtime: 25090.920 ms WITH SET ENABLE_SEQSCAN TO OFF; HashAggregate (cost=3D2943834.84..2944155.21 rows=3D11650 width=3D78) (act= ual time=3D1479.361..1480.641 rows=3D1780 loops=3D1) -> Nested Loop (cost=3D0.00..2942683.39 rows=3D153526 width=3D78) (actu= al time=3D195.690..1345.494 rows=3D38009 loops=3D1) -> Index Scan using liqos_pkey on liquidacionesos l (cost=3D 0.00..3020.21 rows=3D1318 width=3D4) (actual time=3D174.546..666.761 rows= =3D1530 loops=3D1) Filter: ((per_a =3D '2005'::bpchar) AND facturada AND (codigoobrasocial =3D 54)) -> Index Scan using dp_pkey on detalleprestaciones dp (cost=3D 0.00..2214.90 rows=3D1240 width=3D82) (actual time=3D0.333..0.422 rows=3D25 loops=3D1530) Index Cond: (dp.codigoliquidacionos =3D "outer".codigoliquidacionos) Total runtime: 1481.244 ms Thanks again, Alejandro ------=_Part_17787_25182699.1143030233867 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 2006/3/22, Steinar H. Gunderson <sgunderson@bigfoot.com>:
<= blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 2= 04, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:
>= Explains:
> ------------
> With SET ENABLE_SEQSCAN TO ON;
&= gt; HashAggregate  (cost=3D251306.99..251627.36 rows=3D11650 widt= h=3D78)

You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN.

/* St= einar */
--
Homepage: http://www.se= sse.net/

---------------------------(end of broadcast)----------= -----------------
TIP 6: explain analyze is your friend

Sorry, this is the result:

WITH SET ENABLE_SEQSCAN TO ON;

HashAggregate  (cost=3D251306.99..251627.36 rows=3D11650 width=3D78) (= actual time=3D25089.024..25090.340 rows=3D1780 loops=3D1)
  ->  Hash Join  (cost=3D1894.30..250155.54 rows=3D153526 width=3D78) (actual time=3D3190.599..24944.418 rows=3D38009 loops=3D1)
        Hash Cond: ("outer".co= digoliquidacionos =3D "inner".codigoliquidacionos)
        ->  Seq Scan on detalleprestaciones dp  (cost=3D0.00..219621.32 rows=3D5420932 width=3D82) (actual time=3D0.058..23198.852 rows=3D5421786 loops=3D1)
        ->  Hash  (cost=3D1891.01..1891.01 rows=3D1318 width=3D4) (actual time=3D60.777..60.7= 77 rows=3D1530 loops=3D1)
            &nb= sp; ->  Bitmap Heap Scan on liquidacionesos l  (cost=3D43.89..1891.01 rows=3D1318 width=3D4) (actual time=3D1.843..59.574 rows=3D1530 loops=3D1)
            &nb= sp;       Recheck Cond: (codigoobrasocial =3D 54)
            &nb= sp;       Filter: ((per_a =3D '2005'::bpchar) AND facturada)
            &nb= sp;       ->  Bitmap Index Scan on ixliqos_os  (cost=3D0.00..43.89 rows=3D4541 width=3D0) (actual time=3D1.439..1.439 rows=3D4736 loops=3D1)             &nb= sp;             Index Cond: (codigoobrasocial =3D 54)
Total runtime: 25090.920 ms

WITH SET ENABLE_SEQSCAN TO OFF;
HashAggregate  (cost=3D2943834.84..2944155.21 rows=3D11650 width=3D78)= (actual time=3D1479.361..1480.641 rows=3D1780 loops=3D1)
  ->  Nested Loop  (cost=3D0.00..2942683.39 rows=3D153526 width=3D78) (actual time=3D195.690..1345.494 rows=3D38009 loops=3D1)
        ->  Index Scan using liqos_pkey on liquidacionesos l  (cost=3D0.00..3020.21 rows=3D1318 width=3D4) (actual time=3D174.546..666.761 rows=3D1530 loops=3D1)
            &nb= sp; Filter: ((per_a =3D '2005'::bpchar) AND facturada AND (codigoobrasocial =3D 54))
        ->  Index Scan using dp_pkey on detalleprestaciones dp  (cost=3D0.00..2214.90 rows=3D1240 width=3D82) (actual time=3D0.333..0.422 rows=3D25 loops=3D1530)
            &nb= sp; Index Cond: (dp.codigoliquidacionos =3D "outer".codigoliquidacion= os)
Total runtime: 1481.244 ms

Thanks again, Alejandro
------=_Part_17787_25182699.1143030233867--