Re: Index Scans become Seq Scans after VACUUM ANALYSE

On Tue, Apr 16, 2002 at 10:41:57AM -0400, Tom Lane wrote:
> Louis-David Mitterrand <vindex(at)apartia(dot)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_contact is 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::int4 as 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) (actual time=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.99 rows=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.97 rows=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=23039 loops=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.32 rows=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=95427 loops=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)