Hi -hackers,
While testing RC2 on the new servers of one of our customers, I found
a query really slow on the new server which is quite fast on the old
box currently in production (production is 8.1 at the moment).
If I set enable_nestloop to off, the query is fast (the plan is
different from the 8.1 plan though).
I attached:
- the plan with regular configuration;
- the plan after disabling nested loops;
- the plan obtained with 8.1 on the current production box;
- the relevant configuration and schema of the concerned tables.
The plans are really too different to find a narrower test case so
it's the real test case.
Feel free to ask any additional information or tests.
Regards,
--
Guillaume
cityvox_prod=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3RC2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070626 (Red Hat 4.1.2-14)
(1 row)
cityvox_prod=# show shared_buffers;
shared_buffers
----------------
2GB
(1 row)
cityvox_prod=# show work_mem;
work_mem
----------
32MB
(1 row)
cityvox_prod=# show effective_cache_size;
effective_cache_size
----------------------
5GB
(1 row)
cityvox_prod=# show lc_collate;
lc_collate
-------------
fr_FR.UTF-8
(1 row)
cityvox_prod=# show random_page_cost ;
random_page_cost
------------------
2
(1 row)
(same result with a random page cost of 4)
cityvox_prod=# \d association
Table "cityvox.association"
Column | Type |
Modifiers
---------------------+-----------------------------+-----------------------------------------------------------------------
numasso | integer | not null default
nextval(('seq_association_numasso'::text)::regclass)
nomasso | character varying(144) |
nomassoofficiel | character varying(144) | not null
mots_cleasso | character varying(150) |
adremailasso | character varying(200) |
adrurlsitepersoasso | character varying(400) |
adresseasso | character varying(200) |
codepostalasso | character varying(7) |
villeasso | character varying(40) |
codepaysasso | character varying(3) |
dcreaasso | timestamp without time zone | not null default now()
ddermodifasso | timestamp without time zone | not null default now()
nbrclicssiteweb | integer | default 0
codequarasso | character varying(5) | not null
nbradherents | integer | not null default 0
loginmodif | character varying(20) | not null default
'Association'::character varying
logincrea | character varying(20) | not null default
'Association'::character varying
adresseasso2 | character varying(200) |
villepostale | character varying(40) |
codeact | character varying(3) |
wgslat | numeric(11,8) |
wgslon | numeric(11,8) |
vilsup | character varying(100) |
fax | character varying(12) |
telephone | character varying(12) |
dderactivite | timestamp without time zone | not null default now()
assomaj | integer | not null default 0
Indexes:
"pk_association" PRIMARY KEY, btree (numasso)
Foreign-key constraints:
"fk_association_codepays" FOREIGN KEY (codepaysasso) REFERENCES
pays(codepays)
"fk_association_codequar" FOREIGN KEY (codequarasso) REFERENCES
quartier(codequar)
"fk_association_validation" FOREIGN KEY (codeact) REFERENCES
statuttraitement(codestatutttmt)
cityvox_prod=# \d assovil
Table "cityvox.assovil"
Column | Type | Modifiers
---------+----------------------+-----------
numasso | integer | not null
codevil | character varying(3) | not null
Indexes:
"pk_assovil" PRIMARY KEY, btree (numasso, codevil)
Foreign-key constraints:
"fk_assovil_codevil" FOREIGN KEY (codevil) REFERENCES vilsite(codevil) ON
DELETE CASCADE
"fk_assovil_numasso" FOREIGN KEY (numasso) REFERENCES association(numasso)
ON DELETE CASCADE
cityvox_prod=# \d evelieu
Table "cityvox.evelieu"
Column | Type | Modifiers
-----------------+-----------------------------+--------------------
numlieu | integer | not null
numeve | integer | not null
ddebevelieu | timestamp without time zone | not null
dfinevelieu | timestamp without time zone | not null
logincrea | character varying(32) | not null
loginmodif | character varying(32) | not null
dcreaevelieu | timestamp without time zone | not null
ddermajevelieu | timestamp without time zone | not null
inforeservation | character varying(200) |
codestatut | character varying(2) | not null
prolongation | integer | not null default 0
nummanif | integer |
flagphoto | integer | not null default 0
codestatutresa | integer | not null default 0
codemodelivr | character varying(1) |
codetypebillets | character varying(1) |
pushbilletterie | integer | not null default 9
partenaire_cit | integer | not null default 0
numasso | integer |
Indexes:
"pk_evelieu" PRIMARY KEY, btree (numlieu, numeve)
"idx_evelieu_ddebevelieu_trunc" btree (date_trunc('day'::text, ddebevelieu))
"idx_evelieu_dfinevelieu" btree (dfinevelieu)
"idx_evelieu_dfinevelieu_trunc" btree (date_trunc('day'::text, dfinevelieu))
"idx_evelieu_numeve" btree (numeve)
"idx_evelieu_numeve_numlieu_ddebevelieu_dfinevelieu" btree (numeve,
numlieu, ddebevelieu, dfinevelieu)
"idx_evelieu_numeve_numlieu_dfinevelieu" btree (numeve, numlieu,
dfinevelieu)
"idx_evelieu_numeve_nummanif" btree (numeve, nummanif)
"idx_evelieu_numlieu_ddermajevelieu" btree (numlieu, ddermajevelieu)
"idx_on_evelieu_numeve_numlieu_dfinevelieu" btree (numeve, numlieu,
date_trunc('day'::text, dfinevelieu))
Foreign-key constraints:
"fk_evelieu_numasso" FOREIGN KEY (numasso) REFERENCES association(numasso)
"fk_evelieu_ref_codemodelivr" FOREIGN KEY (codemodelivr) REFERENCES
modelivraison(codemodelivr) ON DELETE CASCADE
"fk_evelieu_ref_codetypebillets" FOREIGN KEY (codetypebillets) REFERENCES
typebillets(codetypebillets) ON DELETE CASCADE
"fk_evelieu_ref_eve" FOREIGN KEY (numeve) REFERENCES evenement(numeve) ON
DELETE CASCADE
"fk_evelieu_ref_lieu" FOREIGN KEY (numlieu) REFERENCES lieu(numlieu) ON
DELETE CASCADE
"fk_evelieu_ref_nummanif" FOREIGN KEY (nummanif) REFERENCES
evenement(numeve) ON DELETE CASCADE
"fk_evelieu_ref_statut" FOREIGN KEY (codestatut) REFERENCES
statut(codestatut) ON DELETE CASCADE
"fk_evelieu_statutresa" FOREIGN KEY (codestatutresa) REFERENCES
statutresa(codestatutresa) ON DELETE CASCADE
cityvox_prod=# \d lieu
Table "cityvox.lieu"
Column | Type | Modifiers
----------------------+-----------------------------+------------------------------
numlieu | integer | not null
codequar | character varying(5) | not null
a lot of other fields...
Indexes:
"pk_lieu" PRIMARY KEY, btree (numlieu)
"idx_lieu_identifianturl" UNIQUE, btree (identifianturl)
"idx_lieu_codepostallieu" btree (codepostallieu)
"idx_lieu_codequar_nomlieu_upper_like" btree (codequar,
upper(nomlieu::text) varchar_pattern_ops)
"idx_lieu_codequar_notmoylieu_flagphoto_interditalaune" btree (codequar,
notmoylieu, flagphoto, interditalaune)
"idx_lieu_coordonnees_terrestres" gist (ll_to_earth(wgslat::double
precision, wgslon::double precision))
"idx_lieu_dfinvalidlieu" btree (dfinvalidlieu)
"idx_lieu_libvilpostalelieu" btree (libvilpostalelieu)
"idx_lieu_nomlieu_upper_like" btree (upper(nomlieu::text)
varchar_pattern_ops)
"idx_lieu_numcamerapress" btree (numcamerapress)
"idx_lieu_numlieu_codequar_dfinvalidlieu" btree (numlieu, codequar,
dfinvalidlieu)
"idx_lieu_presenceplus" btree (presenceplus) WHERE presenceplus = 1
"lieu_i_codequar" btree (codequar)
"lieu_i_nomlieu" btree (nomlieu)
Foreign-key constraints:
"fk_lieu_est_situe_quartier" FOREIGN KEY (codequar) REFERENCES
quartier(codequar)
"fk_lieu_ref_sourceinfo" FOREIGN KEY (codesourceinfo) REFERENCES
sourceinfo(codesourceinfo)
"fk_lieu_tyvoielie_tyvoie" FOREIGN KEY (codetyvoie) REFERENCES
tyvoie(codetyvoie)
cityvox_prod=# \d vilquartier
Table "cityvox.vilquartier"
Column | Type | Modifiers
----------------+------------------------+-----------------------------------------
codevil | character varying(3) | not null
codequar | character varying(5) | not null
liblong | character varying(100) | not null
flagintramuros | character varying(1) | not null default 'O'::character
varying
libcourt | character varying(100) | not null
Indexes:
"pk_vilquartier" PRIMARY KEY, btree (codequar, codevil)
"idx_vilquartier_codevil" btree (codevil)
"vilquartier_i_codequar" btree (codequar)
Foreign-key constraints:
"fk_vilqtier_ref_quartier" FOREIGN KEY (codequar) REFERENCES
quartier(codequar) ON DELETE CASCADE
"fk_vilqtier_ref_vilsite" FOREIGN KEY (codevil) REFERENCES vilsite(codevil)
ON DELETE CASCADE
cityvox_prod=# EXPLAIN ANALYZE SELECT a.numasso, a.nomasso, a.nomassoofficiel,
el.numeve, el.numlieu, vq.codequar, toTimestamp(ddebevelieu) as debutevelieu,
toTimestamp(dfinevelieu) as finevelieu FROM association a, evelieu el, assovil
av, lieu l, vilquartier vq WHERE 1=1 AND l.codequar = vq.codequar AND l.numlieu
= el.numlieu AND vq.codevil = av.codevil AND a.numasso = el.numasso AND
a.numasso = av.numasso AND a.codeact = 'V' AND av.codevil = 'LYO' AND
date_trunc('day', el.dfinevelieu) >= date_trunc('day', now()) ORDER BY
el.numeve ASC LIMIT 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3169.91..3169.91 rows=1 width=68) (actual time=8106.727..8106.730
rows=1 loops=1)
-> Sort (cost=3169.91..3169.91 rows=1 width=68) (actual
time=8106.725..8106.726 rows=1 loops=1)
Sort Key: el.numeve
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=7.55..3169.90 rows=1 width=68) (actual
time=5138.594..8106.706 rows=1 loops=1)
Join Filter: ((vq.codequar)::text = (l.codequar)::text)
-> Merge Join (cost=7.55..2905.50 rows=65 width=68) (actual
time=5138.556..8106.465 rows=36 loops=1)
Merge Cond: (el.numasso = a.numasso)
-> Nested Loop (cost=0.00..254537.64 rows=90 width=37)
(actual time=5137.405..8104.863 rows=36 loops=1)
-> Nested Loop (cost=0.00..254387.29 rows=5
width=36) (actual time=5137.375..8104.719 rows=2 loops=1)
Join Filter: (el.numasso = av.numasso)
-> Index Scan using pk_assovil on assovil av
(cost=0.00..32.01 rows=38 width=8) (actual time=0.028..0.238 rows=38 loops=1)
Index Cond: ((codevil)::text =
'LYO'::text)
-> Seq Scan on evelieu el
(cost=0.00..5978.46 rows=57208 width=28) (actual time=0.010..197.039 rows=28019
loops=38)
Filter: (date_trunc('day'::text,
el.dfinevelieu) >= date_trunc('day'::text, now()))
-> Index Scan using idx_vilquartier_codevil on
vilquartier vq (cost=0.00..29.89 rows=18 width=9) (actual time=0.025..0.045
rows=18 loops=2)
Index Cond: ((vq.codevil)::text = 'LYO'::text)
-> Index Scan using pk_association on association a
(cost=0.00..75.50 rows=717 width=39) (actual time=0.017..1.115 rows=727 loops=1)
Filter: ((a.codeact)::text = 'V'::text)
-> Index Scan using pk_lieu on lieu l (cost=0.00..4.05 rows=1
width=9) (actual time=0.003..0.004 rows=1 loops=36)
Index Cond: (l.numlieu = el.numlieu)
Total runtime: 8106.852 ms
cityvox_prod=# set enable_nestloop = off;
SET
cityvox_prod=# EXPLAIN ANALYZE SELECT a.numasso, a.nomasso, a.nomassoofficiel,
el.numeve, el.numlieu, vq.codequar, toTimestamp(ddebevelieu) as debutevelieu,
toTimestamp(dfinevelieu) as finevelieu FROM association a, evelieu el, assovil
av, lieu l, vilquartier vq WHERE 1=1 AND l.codequar = vq.codequar AND l.numlieu
= el.numlieu AND vq.codevil = av.codevil AND a.numasso = el.numasso AND
a.numasso = av.numasso AND a.codeact = 'V' AND av.codevil = 'LYO' AND
date_trunc('day', el.dfinevelieu) >= date_trunc('day', now()) ORDER BY
el.numeve ASC LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11724.96..11724.97 rows=1 width=68) (actual time=209.723..209.726
rows=1 loops=1)
-> Sort (cost=11724.96..11724.97 rows=1 width=68) (actual
time=209.721..209.722 rows=1 loops=1)
Sort Key: el.numeve
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=4760.99..11724.95 rows=1 width=68) (actual
time=182.555..209.699 rows=1 loops=1)
Hash Cond: ((l.codequar)::text = (vq.codequar)::text)
-> Hash Join (cost=4740.63..11704.47 rows=4 width=72) (actual
time=71.275..209.553 rows=2 loops=1)
Hash Cond: (l.numlieu = el.numlieu)
-> Seq Scan on lieu l (cost=0.00..6481.40 rows=128640
width=9) (actual time=0.006..85.340 rows=128640 loops=1)
-> Hash (cost=4740.58..4740.58 rows=4 width=67) (actual
time=45.302..45.302 rows=2 loops=1)
-> Hash Join (cost=835.85..4740.58 rows=4
width=67) (actual time=20.648..45.296 rows=2 loops=1)
Hash Cond: (el.numasso = a.numasso)
-> Bitmap Heap Scan on evelieu el
(cost=759.63..4449.79 rows=57208 width=28) (actual time=8.854..27.657
rows=28019 loops=1)
Recheck Cond: (date_trunc('day'::text,
dfinevelieu) >= date_trunc('day'::text, now()))
-> Bitmap Index Scan on
idx_evelieu_dfinevelieu_trunc (cost=0.00..745.33 rows=57208 width=0) (actual
time=8.707..8.707 rows=28019 loops=1)
Index Cond:
(date_trunc('day'::text, dfinevelieu) >= date_trunc('day'::text, now()))
-> Hash (cost=75.84..75.84 rows=30 width=47)
(actual time=1.808..1.808 rows=26 loops=1)
-> Hash Join (cost=49.16..75.84
rows=30 width=47) (actual time=1.546..1.783 rows=26 loops=1)
Hash Cond: (av.numasso = a.numasso)
-> Seq Scan on assovil av
(cost=0.00..26.24 rows=38 width=8) (actual time=0.015..0.205 rows=38 loops=1)
Filter: ((codevil)::text =
'LYO'::text)
-> Hash (cost=40.20..40.20
rows=717 width=39) (actual time=1.519..1.519 rows=717 loops=1)
-> Seq Scan on association
a (cost=0.00..40.20 rows=717 width=39) (actual time=0.005..0.886 rows=717
loops=1)
Filter:
((codeact)::text = 'V'::text)
-> Hash (cost=20.14..20.14 rows=18 width=9) (actual
time=0.088..0.088 rows=18 loops=1)
-> Bitmap Heap Scan on vilquartier vq (cost=2.39..20.14
rows=18 width=9) (actual time=0.043..0.063 rows=18 loops=1)
Recheck Cond: ((codevil)::text = 'LYO'::text)
-> Bitmap Index Scan on idx_vilquartier_codevil
(cost=0.00..2.39 rows=18 width=0) (actual time=0.035..0.035 rows=18 loops=1)
Index Cond: ((codevil)::text = 'LYO'::text)
Total runtime: 209.839 ms
cityvox_prod=# EXPLAIN ANALYZE SELECT a.numasso, a.nomasso, a.nomassoofficiel,
el.numeve, el.numlieu, vq.codequar, toTimestamp(ddebevelieu) as debutevelieu,
toTimestamp(dfinevelieu) as finevelieu FROM association a, evelieu el, assovil
av, lieu l, vilquartier vq WHERE 1=1 AND l.codequar = vq.codequar AND l.numlieu
= el.numlieu AND vq.codevil = av.codevil AND a.numasso = el.numasso AND
a.numasso = av.numasso AND a.codeact = 'V' AND av.codevil = 'LYO' AND
date_trunc('day', el.dfinevelieu) >= date_trunc('day', now()) ORDER BY
el.numeve ASC LIMIT 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3964.90..3964.91 rows=1 width=77) (actual time=305.762..305.775
rows=1 loops=1)
-> Sort (cost=3964.90..3964.91 rows=1 width=77) (actual
time=305.752..305.757 rows=1 loops=1)
Sort Key: el.numeve
-> Nested Loop (cost=332.95..3964.89 rows=1 width=77) (actual
time=128.550..305.711 rows=1 loops=1)
-> Nested Loop (cost=332.95..3960.88 rows=1 width=84) (actual
time=128.454..305.557 rows=2 loops=1)
-> Nested Loop (cost=332.95..3956.85 rows=1 width=76)
(actual time=128.409..305.443 rows=2 loops=1)
-> Hash Join (cost=332.95..3953.41 rows=1
width=73) (actual time=88.687..303.937 rows=66 loops=1)
Hash Cond: ("outer".numasso = "inner".numasso)
-> Bitmap Heap Scan on evelieu el
(cost=282.57..3757.93 rows=29018 width=28) (actual time=44.335..178.776
rows=28512 loops=1)
Recheck Cond: (date_trunc('day'::text,
dfinevelieu) >= date_trunc('day'::text, now()))
-> Bitmap Index Scan on
idx_evelieu_dfinevelieu_trunc (cost=0.00..282.57 rows=29018 width=0) (actual
time=43.811..43.811 rows=28512 loops=1)
Index Cond:
(date_trunc('day'::text, dfinevelieu) >= date_trunc('day'::text, now()))
-> Hash (cost=48.54..48.54 rows=738
width=45) (actual time=9.575..9.575 rows=718 loops=1)
-> Seq Scan on association a
(cost=0.00..48.54 rows=738 width=45) (actual time=0.015..4.835 rows=718 loops=1)
Filter: ((codeact)::text =
'V'::text)
-> Index Scan using pk_assovil on assovil av
(cost=0.00..3.43 rows=1 width=11) (actual time=0.014..0.014 rows=0 loops=66)
Index Cond: (("outer".numasso = av.numasso)
AND ((av.codevil)::text = 'LYO'::text))
-> Index Scan using pk_lieu on lieu l (cost=0.00..4.01
rows=1 width=12) (actual time=0.026..0.031 rows=1 loops=2)
Index Cond: (l.numlieu = "outer".numlieu)
-> Index Scan using pk_vilquartier on vilquartier vq
(cost=0.00..3.99 rows=1 width=15) (actual time=0.032..0.035 rows=0 loops=2)
Index Cond: ((("outer".codequar)::text =
(vq.codequar)::text) AND ('LYO'::text = (vq.codevil)::text))
Total runtime: 306.182 ms
(22 rows)
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
