Re: Sequence Scan vs. Index scan

["Alejandro D. Burne" <alejandro(dot)dburne(at)gmail(dot)com>]

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.32 rows=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.89
rows=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