curius behavior of planner after analyze, (Pg7.5) - Mailing list pgsql-general

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_pripad_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_partner op
where lcase(value) like '%zemek%' and t.sp_op_id=s.sp_op_id and
t.techr_id=q.t
echr_id and s.obchodni_pripad_id=o.obchodni_pripad_id and
p.produkt_id=s.produkt_id and o.partner_id=op.partner_id;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=512.53..551.66 rows=3 width=1108) (actual
time=72.236..72.740 rows=2 loops=1)
   ->  Nested Loop  (cost=512.53..533.75 rows=3 width=963) (actual
time=72.176..72.637 rows=2 loops=1)
         ->  Hash Join  (cost=512.53..515.87 rows=3 width=814) (actual
time=72.097..72.519 rows=2 loops=1)
               Hash Cond: ("outer".produkt_id = "inner".produkt_id)
               ->  Seq Scan on produkt p  (cost=0.00..2.66 rows=132
width=520) (actual time=0.010..0.259 rows=132 loops=1)
               ->  Hash  (cost=512.52..512.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_id)
                                 ->  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.09 rows=19
width=149) (actual time=0.162..0.162 rows=0 loops=1)
                                       ->  Seq Scan on techreq q
(cost=0.00..1.09 rows=19 width=149) (actual time=0.039..0.118 rows=19
loops=1)
                           ->  Index Scan using cccc on sp_op_produkt s
(cost=0.00..6.00 rows=1 width=12) (actual time=0.043..0.049 rows=1
loops=2)
                                 Index Cond: ("outer".sp_op_id =
s.sp_op_id)
         ->  Index Scan using obchodni_pripad_pkey on obchodni_pripad o
(cost=0.00..5.95 rows=1 width=153) (actual time=0.029..0.033 rows=1
loops=2)
               Index Cond: ("outer".obchodni_pripad_id =
o.obchodni_pripad_id)
   ->  Index Scan using obchodni_partner_pkey on obchodni_partner op
(cost=0.00..5.96 rows=1 width=149) (actual time=0.022..0.026 rows=1
loops=2)
         Index Cond: ("outer".partner_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_pripad_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_partner op
where lcase(value) like '%zemek%' and t.sp_op_id=s.sp_op_id and
t.techr_id=q.t
echr_id and s.obchodni_pripad_id=o.obchodni_pripad_id and
p.produkt_id=s.produkt_id and o.partner_id=op.partner_id;

QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------
---------------
 Hash Join  (cost=683.73..750.25 rows=35 width=114) (actual
time=89.755..95.817 rows=2 loops=1)
   Hash Cond: ("outer".techr_id = "inner".techr_id)
   ->  Hash Join  (cost=682.59..748.75 rows=35 width=100) (actual
time=88.764..94.811 rows=2 loops=1)
         Hash Cond: ("outer".partner_id = "inner".partner_id)
         ->  Seq Scan on obchodni_partner op  (cost=0.00..53.99 rows=2399
width=28) (actual time=0.066..6.029 rows=2399 loops=1)
         ->  Hash  (cost=682.50..682.50 rows=35 width=76) (actual
time=85.297..85.297 rows=0 loops=1)
               ->  Hash Join  (cost=621.19..682.50 rows=35 width=76)
(actual time=77.796..85.258 rows=2 loops=1)
                     Hash Cond: ("outer".obchodni_pripad_id =
"inner".obchodni_pripad_id)
                     ->  Seq Scan on obchodni_pripad o  (cost=0.00..48.57
rows=2514 width=30) (actual time=0.008..4.948 rows=2514 loops=1)
                     ->  Hash  (cost=621.10..621.10 rows=35 width=50)
(actual time=76.866..76.866 rows=0 loops=1)
                           ->  Hash Join  (cost=616.61..621.10 rows=35
width=50) (actual time=76.429..76.853 rows=2 loops=1)
                                 Hash Cond: ("outer".produkt_id =
"inner".produkt_id)
                                 ->  Seq Scan on produkt p
(cost=0.00..2.66 rows=132 width=29) (actual time=0.012..0.260 rows=132
loops=1)
                                 ->  Hash  (cost=616.53..616.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.69 rows=1 width=12) (actual
time=0.277..0.284 r
ows=1 loops=2)
                                                   Index Cond:
("outer".sp_op_id = s.sp_op_id)
   ->  Hash  (cost=1.09..1.09 rows=19 width=22) (actual time=0.195..0.195
rows=0 loops=1)
         ->  Seq Scan on techreq q  (cost=0.00..1.09 rows=19 width=22)
(actual time=0.081..0.148 rows=19 loops=1)
 Total runtime: 96.596 ms
(22 řádek)