473,699 Members | 2,119 Online

# curius behavior of planner after analyze, (Pg7.5)

Hello

I found following select where plan before vacuum analyse is better than
after vacuum. What I have to do? I can change only set statistic for any
column. How I can find which columns I have to alter?

Regards
Pavel Stehule

intra_test=# explain analyze select t.value, t.sp_op_id,
s.obchodni_prip ad_id, o.popis, p.popis, op.jmeno, q.comment, op.partner_id
from techbox t, sp_o
p_produkt s, techreq q, obchodni_pripad o, produkt p, obchodni_partne r op
where lcase(value) like '%zemek%' and t.sp_op_id=s.sp _op_id and
t.techr_id=q.t
p.produkt_id=s. produkt_id and o.partner_id=op .partner_id;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=512.53..5 51.66 rows=3 width=1108) (actual
time=72.236..72 .740 rows=2 loops=1)
-> Nested Loop (cost=512.53..5 33.75 rows=3 width=963) (actual
time=72.176..72 .637 rows=2 loops=1)
-> Hash Join (cost=512.53..5 15.87 rows=3 width=814) (actual
time=72.097..72 .519 rows=2 loops=1)
Hash Cond: ("outer".produk t_id = "inner".produkt _id)
-> Seq Scan on produkt p (cost=0.00..2.6 6 rows=132
width=520) (actual time=0.010..0.2 59 rows=132 loops=1)
-> Hash (cost=512.52..5 12.52 rows=4 width=302) (actual
time=71.998..71 .998 rows=0 loops=1)
-> Nested Loop (cost=1.14..512 .52 rows=4 width=302)
(actual time=0.807..71. 989 rows=2 loops=1)
-> Hash Join (cost=1.14..488 .50 rows=4
width=294) (actual time=0.724..71. 853 rows=2 loops=1)
Hash Cond: ("outer".techr_ id =
"inner".techr_i d)
-> Seq Scan on techbox t
(cost=0.00..487 .16 rows=35 width=153) (actual time=0.535..71. 649 rows=2
loops=1)
Filter: (lower((value): :text) ~~
'%zemek%'::text )
-> Hash (cost=1.09..1.0 9 rows=19
width=149) (actual time=0.162..0.1 62 rows=0 loops=1)
-> Seq Scan on techreq q
(cost=0.00..1.0 9 rows=19 width=149) (actual time=0.039..0.1 18 rows=19
loops=1)
-> Index Scan using cccc on sp_op_produkt s
(cost=0.00..6.0 0 rows=1 width=12) (actual time=0.043..0.0 49 rows=1
loops=2)
Index Cond: ("outer".sp_op_ id =
s.sp_op_id)
(cost=0.00..5.9 5 rows=1 width=153) (actual time=0.029..0.0 33 rows=1
loops=2)
-> Index Scan using obchodni_partne r_pkey on obchodni_partne r op
(cost=0.00..5.9 6 rows=1 width=149) (actual time=0.022..0.0 26 rows=1
loops=2)
Index Cond: ("outer".partne r_id = op.partner_id)
Total runtime: 73.358 ms
(20 øádek)

intra_test=# VACUUM ANALYZE;
VACUUM
intra_test=# explain analyze select t.value, t.sp_op_id,
s.obchodni_prip ad_id, o.popis, p.popis, op.jmeno, q.comment, op.partner_id
from techbox t, sp_o
p_produkt s, techreq q, obchodni_pripad o, produkt p, obchodni_partne r op
where lcase(value) like '%zemek%' and t.sp_op_id=s.sp _op_id and
t.techr_id=q.t
p.produkt_id=s. produkt_id and o.partner_id=op .partner_id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------
---------------
Hash Join (cost=683.73..7 50.25 rows=35 width=114) (actual
time=89.755..95 .817 rows=2 loops=1)
Hash Cond: ("outer".techr_ id = "inner".techr_i d)
-> Hash Join (cost=682.59..7 48.75 rows=35 width=100) (actual
time=88.764..94 .811 rows=2 loops=1)
Hash Cond: ("outer".partne r_id = "inner".partner _id)
-> Seq Scan on obchodni_partne r op (cost=0.00..53. 99 rows=2399
width=28) (actual time=0.066..6.0 29 rows=2399 loops=1)
-> Hash (cost=682.50..6 82.50 rows=35 width=76) (actual
time=85.297..85 .297 rows=0 loops=1)
-> Hash Join (cost=621.19..6 82.50 rows=35 width=76)
(actual time=77.796..85 .258 rows=2 loops=1)
-> Seq Scan on obchodni_pripad o (cost=0.00..48. 57
rows=2514 width=30) (actual time=0.008..4.9 48 rows=2514 loops=1)
-> Hash (cost=621.10..6 21.10 rows=35 width=50)
(actual time=76.866..76 .866 rows=0 loops=1)
-> Hash Join (cost=616.61..6 21.10 rows=35
width=50) (actual time=76.429..76 .853 rows=2 loops=1)
Hash Cond: ("outer".produk t_id =
"inner".produkt _id)
-> Seq Scan on produkt p
(cost=0.00..2.6 6 rows=132 width=29) (actual time=0.012..0.2 60 rows=132
loops=1)
-> Hash (cost=616.53..6 16.53 rows=35
width=29) (actual time=76.287..76 .287 rows=0 loops=1)
-> Nested Loop (cost=0.00..616 .53
rows=35 width=29) (actual time=1.055..76. 274 rows=2 loops=1)
-> Seq Scan on techbox t
(cost=0.00..487 .16 rows=35 width=21) (actual time=0.621..75. 650 rows=2
loops=1)
Filter:
(lower((value): :text) ~~ '%zemek%'::text )
-> Index Scan using cccc on
sp_op_produkt s (cost=0.00..3.6 9 rows=1 width=12) (actual
time=0.277..0.2 84 r
ows=1 loops=2)
Index Cond:
("outer".sp_op_ id = s.sp_op_id)
-> Hash (cost=1.09..1.0 9 rows=19 width=22) (actual time=0.195..0.1 95
rows=0 loops=1)
-> Seq Scan on techreq q (cost=0.00..1.0 9 rows=19 width=22)
(actual time=0.081..0.1 48 rows=19 loops=1)
Total runtime: 96.596 ms
(22 øádek)

TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
0 948

This thread has been closed and replies have been disabled. Please start a new discussion.