Re: Index Scans become Seq Scans after VACUUM ANALYSE - Mailing list pgsql-hackers

On Tue, Apr 16, 2002 at 10:41:57AM -0400, Tom Lane wrote:
> Louis-David Mitterrand <vindex@apartia.org> writes:
> > While trying to optimise a query I found that running VACUUM ANALYSE
> > changed all the Index Scans to Seq Scans and that the only way to revert
> > to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.
>
> EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
> Also, what does the pg_stats view show for these tables?

Thanks, pg_stats output is rather big so I attached it in a separate
file. Here are the EXPLAIN ANALYZE ouputs:

*********************************
* 1) With "enable_seqscan = 0": *
*********************************

gesci5=# explain analyse select p.id_prospect, p.position_prospect, initcap(p1.nom) as nom, initcap(p1.prenom) as
prenom,a1.no_tel, a1.no_portable, p.dernier_contact, cn.id_contact, cn.id_vendeur, cn.id_operation, case when
p.dernier_contactis not null then cn.date_contact::abstime::int4 else p.cree_le::abstime::int4 end as date_contact,
cn.type_contact,cn.nouveau_rdv::abstime::int4 as nouveau_rdv, cn.date_echeance::abstime::int4 as date_echeance,
cn.date_reponse::abstime::int4as date_reponse from prospect p left join personne p1 on (p1.id_personne =
p.id_personne1)left join adresse a1 on (a1.id_adresse = p1.id_adresse_principale) left join contact cn on
(p.dernier_contact= cn.id_contact) where (p.abandon is null or p.abandon != 'O')  order by cn.date_contact desc; 
NOTICE:  QUERY PLAN:

Sort  (cost=49442.99..49442.99 rows=24719 width=123) (actual time=7281.98..7319.91 rows=23038 loops=1)
  ->  Hash Join  (cost=14146.79..46656.05 rows=24719 width=123) (actual time=2619.85..6143.47 rows=23038 loops=1)
        ->  Merge Join  (cost=9761.33..40724.83 rows=24719 width=66) (actual time=2061.31..3362.49 rows=23038 loops=1)
              ->  Sort  (cost=9761.33..9761.33 rows=24719 width=49) (actual time=1912.73..1961.61 rows=23038 loops=1)
                    ->  Merge Join  (cost=0.00..7485.53 rows=24719 width=49) (actual time=42.98..1264.63 rows=23038
loops=1)
                          ->  Index Scan using prospect_personne1 on prospect p  (cost=0.00..4322.18 rows=24719
width=22)(actual time=0.28..528.42 rows=23038 loops=1) 
                          ->  Index Scan using personne_pkey on personne p1  (cost=0.00..2681.90 rows=44271 width=27)
(actualtime=0.18..384.11 rows=44302 loops=1) 
              ->  Index Scan using adresse_pkey on adresse a1  (cost=0.00..30354.16 rows=95425 width=17) (actual
time=0.44..738.99rows=95456 loops=1) 
        ->  Hash  (cost=3242.09..3242.09 rows=30224 width=57) (actual time=557.04..557.04 rows=0 loops=1)
              ->  Index Scan using contact_pkey on contact cn  (cost=0.00..3242.09 rows=30224 width=57) (actual
time=0.26..457.97rows=30224 loops=1) 
Total runtime: 7965.74 msec

EXPLAIN

*********************************
* 2) With "enable_seqscan = 1": *
*********************************

NOTICE:  QUERY PLAN:

Sort  (cost=18622.67..18622.67 rows=24719 width=123) (actual time=10329.09..10367.06 rows=23039 loops=1)
  ->  Hash Join  (cost=10034.30..15835.73 rows=24719 width=123) (actual time=1644.04..9397.53 rows=23039 loops=1)
        ->  Hash Join  (cost=8074.99..12330.65 rows=24719 width=66) (actual time=1110.05..6475.65 rows=23039 loops=1)
              ->  Hash Join  (cost=2088.54..4629.65 rows=24719 width=49) (actual time=385.33..2763.91 rows=23039
loops=1)
                    ->  Seq Scan on prospect p  (cost=0.00..1289.35 rows=24719 width=22) (actual time=0.34..361.31
rows=23039loops=1) 
                    ->  Hash  (cost=1106.71..1106.71 rows=44271 width=27) (actual time=381.91..381.91 rows=0 loops=1)
                          ->  Seq Scan on personne p1  (cost=0.00..1106.71 rows=44271 width=27) (actual
time=0.15..246.32rows=44272 loops=1) 
              ->  Hash  (cost=2561.25..2561.25 rows=95425 width=17) (actual time=723.15..723.15 rows=0 loops=1)
                    ->  Seq Scan on adresse a1  (cost=0.00..2561.25 rows=95425 width=17) (actual time=0.17..452.55
rows=95427loops=1) 
        ->  Hash  (cost=1036.24..1036.24 rows=30224 width=57) (actual time=532.87..532.87 rows=0 loops=1)
              ->  Seq Scan on contact cn  (cost=0.00..1036.24 rows=30224 width=57) (actual time=2.54..302.49 rows=30225
loops=1)
Total runtime: 10901.85 msec

EXPLAIN


--
 HIPPOLYTE: Mais quels soins désormais peuvent me retarder ?
            Assez dans les forêts mon oisive jeunesse
            Sur de vils ennemis a montré son adresse.
                                          (Phèdre, J-B Racine, acte 3, scène 5)