Hello,
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.
Seq Scans are much slower for that specific query. Why does Postgres
switch to that method?
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
Output with "enable_seqscan = 0":
gesci5=# explain 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)
-> Hash Join (cost=14146.79..46656.05 rows=24719 width=123)
-> Merge Join (cost=9761.33..40724.83 rows=24719 width=66)
-> Sort (cost=9761.33..9761.33 rows=24719 width=49)
-> Merge Join (cost=0.00..7485.53
rows=24719 width=49)
-> Index Scan using
prospect_personne1 on prospect p (cost=0.00..4322.18 rows=24719 width=22)
-> Index Scan using
personne_pkey on personne p1 (cost=0.00..2681.90 rows=44271 width=27)
-> Index Scan using adresse_pkey on adresse a1
(cost=0.00..30354.16 rows=95425 width=17)
-> Hash (cost=3242.09..3242.09 rows=30224 width=57)
-> Index Scan using contact_pkey on contact cn
(cost=0.00..3242.09 rows=30224 width=57)
Output with "enable_seqscan = 1":
Sort (cost=18622.67..18622.67 rows=24719 width=123)
-> Hash Join (cost=10034.30..15835.73 rows=24719 width=123)
-> Hash Join (cost=8074.99..12330.65 rows=24719 width=66)
-> Hash Join (cost=2088.54..4629.65 rows=24719
width=49)
-> Seq Scan on prospect p
(cost=0.00..1289.35 rows=24719 width=22)
-> Hash (cost=1106.71..1106.71
rows=44271 width=27)
-> Seq Scan on personne p1
(cost=0.00..1106.71 rows=44271 width=27)
-> Hash (cost=2561.25..2561.25 rows=95425 width=17)
-> Seq Scan on adresse a1
(cost=0.00..2561.25 rows=95425 width=17)
-> Hash (cost=1036.24..1036.24 rows=30224 width=57)
-> Seq Scan on contact cn (cost=0.00..1036.24
rows=30224 width=57)
--
OENONE: Rebelle à tous nos soins, sourde à tous nos discours,
Voulez-vous sans pitié laisser finir vos jours ?
(Phèdre, J-B Racine, acte 1, scène 3)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
