Re: Indexes not always used after inserts/updates/vacuum

On Wed, 27 Feb 2002 at 22:57, Tom Lane wrote:

> Also, to put the rubber to the road: if you force an indexscan by
> doing "set enable_seqscan = off", does it get faster or slower?
> (EXPLAIN ANALYZE would be useful here.)

I've just found a case where forcing indexscans results in much higher
speed. On the the 350000 rows table mentioned in my other post after a
"VACUUM FULL ANALYZE":

max=# set enable_seqscan to false;
max=# EXPLAIN analyze
    SELECT count(foo.id) FROM foo, bar
    WHERE foo.id = bar.ref2foo;
Aggregate    (cost=27513.65..27513.65 rows=1 width=8)
        (actual time=652.38..652.38 rows=1 loops=1)
  ->  Merge Join
        (cost=0.00..27417.57 rows=38431 width=8)
        (actual time=0.06..603.02 rows=38431 loops=1)
        ->  Index Scan using foo_pkey on foo
        (cost=0.00..25153.18 rows=352072 width=4)
        (actual time=0.03..157.57 rows=38432 loops=1)
        ->  Index Scan using idx_bar_ref2foo on bar
        (cost=0.00..807.74 rows=38431 width=4)
        (actual time=0.02..170.25 rows=38431 loops=1)
Total runtime: 652.58 msec
               ^^^^^^^^^^^
max=# set enable_seqscan to true;
max=# EXPLAIN analyze
    SELECT count(foo.id) FROM foo, bar
    WHERE foo.id = bar.ref2foo;

Aggregate
        (cost=18560.65..18560.65 rows=1 width=8)
        (actual time=4951.57..4951.57 rows=1 loops=1)
  ->  Hash Join
        (cost=911.39..18464.58 rows=38431 width=8)
        (actual time=653.26..4905.37 rows=38431 loops=1)
        ->  Seq Scan on foo
        (cost=0.00..9251.72 rows=352072 width=4)
        (actual time=0.02..769.60 rows=352072 loops=1)
        ->  Hash
        (cost=683.31..683.31 rows=38431 width=4)
        (actual time=140.60..140.60 rows=0 loops=1)
              ->  Seq Scan on bar
        (cost=0.00..683.31 rows=38431 width=4)
        (actual time=0.02..78.57 rows=38431 loops=1)
Total runtime: 4951.70 msec
               ^^^^^^^^^^^^

I've reproduced that several times. Even on a newly started postmaster
the query takes less than 2.5 seconds with seqscans swited off.

cu
    Reinhard