Re: Sequence Scan vs. Index scan

2006/3/22, Steinar H. Gunderson <sgunderson(at)bigfoot(dot)com>:
>
> On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:
> > Explains:
> > ------------
> > With SET ENABLE_SEQSCAN TO ON;
> > HashAggregate (cost=251306.99..251627.36 rows=11650 width=78)
>
> 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=251306.99..251627.36 rows=11650 width=78) (actual time=
25089.024..25090.340 rows=1780 loops=1)
-> Hash Join (cost=1894.30..250155.54 rows=153526 width=78) (actual
time=3190.599..24944.418 rows=38009 loops=1)
Hash Cond: ("outer".codigoliquidacionos =
"inner".codigoliquidacionos)
-> Seq Scan on detalleprestaciones dp
(cost=0.00..219621.32rows=5420932 width=82) (actual time=
0.058..23198.852 rows=5421786 loops=1)
-> Hash (cost=1891.01..1891.01 rows=1318 width=4) (actual time=
60.777..60.777 rows=1530 loops=1)
-> Bitmap Heap Scan on liquidacionesos l (cost=
43.89..1891.01 rows=1318 width=4) (actual time=1.843..59.574 rows=1530
loops=1)
Recheck Cond: (codigoobrasocial = 54)
Filter: ((per_a = '2005'::bpchar) AND facturada)
-> Bitmap Index Scan on ixliqos_os
(cost=0.00..43.89rows=4541 width=0) (actual time=
1.439..1.439 rows=4736 loops=1)
Index Cond: (codigoobrasocial = 54)
Total runtime: 25090.920 ms

WITH SET ENABLE_SEQSCAN TO OFF;
HashAggregate (cost=2943834.84..2944155.21 rows=11650 width=78) (actual
time=1479.361..1480.641 rows=1780 loops=1)
-> Nested Loop (cost=0.00..2942683.39 rows=153526 width=78) (actual
time=195.690..1345.494 rows=38009 loops=1)
-> Index Scan using liqos_pkey on liquidacionesos l (cost=
0.00..3020.21 rows=1318 width=4) (actual time=174.546..666.761 rows=1530
loops=1)
Filter: ((per_a = '2005'::bpchar) AND facturada AND
(codigoobrasocial = 54))
-> Index Scan using dp_pkey on detalleprestaciones dp (cost=
0.00..2214.90 rows=1240 width=82) (actual time=0.333..0.422 rows=25
loops=1530)
Index Cond: (dp.codigoliquidacionos =
"outer".codigoliquidacionos)
Total runtime: 1481.244 ms

Thanks again, Alejandro