Re: possible wrong query plan on pg 8.3.5, - Mailing list pgsql-performance

Цитат от Tom Lane <tgl@sss.pgh.pa.us>:

> zz_11@mail.bg writes:
>> I am running a relativ complex query on pg 8.3.5 and have (possible)
>> wrong query plan.
>> ...
>> If I run the query without thle last part : and n.num like '191%'
>> it work ok as speed ~ 30 sec on not very big db.
>> If I run the full query it take very long time to go ( i never waited
>> to the end but it take  > 60 min.)
>
> I'm betting that it's badly underestimating the number of rows
> satisfying the LIKE condition:
>
>>                                               ->  Index Scan using
>> i_nomen_num on a_nomen n  (cost=0.00..56.39 rows=24 width=128)
>>                                                     Index Cond:
>> (((num)::text >= '191'::text) AND ((num)::text < '192'::text))
>>                                                     Filter:
>> ((num)::text ~~ '191%'::text)
>
> Is 24 the right number of rows for that, or anywhere close?  If not, try
> raising the statistics target for this table.
>
>             regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>

Hi Tom,

Yes, 24 is relative ok ( the real number is 20).
And the statistic target for the database is 800 at the moment. If
needet I can set it to 1000 ( the maximum).

Also I waited to the end of this query to gather info for explain analyze.
It is it:

  explain analyze  select d.ids from a_doc d  join a_sklad s on
(d.ids=s.ids_doc)  join a_nomen n on (n.ids=s.ids_num)  join a_nom_gr
nmgr on (nmgr.ids=n.ids_grupa)  join a_gar_prod_r gr on
(gr.ids_a_sklad=s.ids and gr.sernum!='ok')  join a_location l on
(l.ids=s.ids_sklad)  join a_klienti kl on (kl.ids=d.ids_ko)  left
outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka)  left outer join
a_slujiteli slu on (slu.ids=d.ids_slu_targ)  where d.op=1  AND
d.date_op >= 12320 AND d.date_op <= 12362 and n.num like '191%';

                    QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop Left Join  (cost=63.64..133732.47 rows=4 width=64)
(actual time=616059.833..1314396.823 rows=91 loops=1)
    ->  Nested Loop  (cost=63.64..133699.35 rows=4 width=128) (actual
time=616033.205..1313991.756 rows=91 loops=1)
          ->  Nested Loop  (cost=63.64..133688.22 rows=4 width=192)
(actual time=616033.194..1313991.058 rows=91 loops=1)
                ->  Nested Loop Left Join  (cost=63.64..133687.10
rows=4 width=256) (actual time=616033.183..1313936.577 rows=91 loops=1)
                      ->  Nested Loop  (cost=63.64..133685.78 rows=4
width=320) (actual time=616033.177..1313929.258 rows=91 loops=1)
                            ->  Nested Loop  (cost=63.64..133646.56
rows=6 width=384) (actual time=616007.069..1313008.701 rows=91 loops=1)
                                  ->  Nested Loop
(cost=63.64..127886.54 rows=2833 width=192) (actual
time=376.309..559763.450 rows=211357 loops=1)
                                        ->  Nested Loop
(cost=63.64..107934.83 rows=13709 width=256) (actual
time=224.058..148475.499 rows=370803 loops=1)
                                              ->  Index Scan using
i_nomen_num on a_nomen n  (cost=0.00..56.39 rows=24 width=128) (actual
time=15.702..198.049 rows=20 loops=1)
                                                    Index Cond:
(((num)::text >= '191'::text) AND ((num)::text < '192'::text))
                                                    Filter:
((num)::text ~~ '191%'::text)
                                              ->  Bitmap Heap Scan on
a_sklad s  (cost=63.64..4480.23 rows=1176 width=256) (actual
time=93.223..7398.764 rows=18540 loops=20)
                                                    Recheck Cond:
(s.ids_num = n.ids)
                                                    ->  Bitmap Index
Scan on i_sklad_ids_num  (cost=0.00..63.34 rows=1176 width=0) (actual
time=78.430..78.430 rows=18540 loops=20)
                                                          Index Cond:
(s.ids_num = n.ids)
                                        ->  Index Scan using
i_a_gar_prod_r_ids_a_sklad on a_gar_prod_r gr  (cost=0.00..1.44 rows=1
width=64) (actual time=1.098..1.108 rows=1 loops=370803)
                                              Index Cond:
(gr.ids_a_sklad = s.ids)
                                              Filter: (gr.sernum <> 'ok'::text)
                                  ->  Index Scan using a_doc_pkey on
a_doc d  (cost=0.00..2.02 rows=1 width=256) (actual time=3.563..3.563
rows=0 loops=211357)
                                        Index Cond: (d.ids = s.ids_doc)
                                        Filter: ((d.date_op >= 12320)
AND (d.date_op <= 12362) AND (d.op = 1))
                            ->  Index Scan using a_klienti_pkey on
a_klienti kl  (cost=0.00..6.53 rows=1 width=64) (actual
time=10.109..10.113 rows=1 loops=91)
                                  Index Cond: (kl.ids = d.ids_ko)
                      ->  Index Scan using a_slujiteli_pkey on
a_slujiteli sl  (cost=0.00..0.32 rows=1 width=64) (actual
time=0.078..0.078 rows=0 loops=91)
                            Index Cond: (sl.ids = d.ids_slu_ka)
                ->  Index Scan using a_location_pkey on a_location l
(cost=0.00..0.27 rows=1 width=64) (actual time=0.596..0.597 rows=1
loops=91)
                      Index Cond: (l.ids = s.ids_sklad)
          ->  Index Scan using a_nom_gr_pkey on a_nom_gr nmgr
(cost=0.00..2.77 rows=1 width=64) (actual time=0.005..0.006 rows=1
loops=91)
                Index Cond: (nmgr.ids = n.ids_grupa)
    ->  Index Scan using a_slujiteli_pkey on a_slujiteli slu
(cost=0.00..8.27 rows=1 width=64) (actual time=4.448..4.449 rows=1
loops=91)
          Index Cond: (slu.ids = d.ids_slu_targ)
  Total runtime: 1314397.153 ms
(32 rows)


And if I try this query for second time it is working very fast:


-----------------------------------------
  Nested Loop Left Join  (cost=63.64..133732.47 rows=4 width=64)
(actual time=9438.195..29429.861 rows=91 loops=1)
    ->  Nested Loop  (cost=63.64..133699.35 rows=4 width=128) (actual
time=9438.155..29363.045 rows=91 loops=1)
          ->  Nested Loop  (cost=63.64..133688.22 rows=4 width=192)
(actual time=9438.145..29355.229 rows=91 loops=1)
                ->  Nested Loop Left Join  (cost=63.64..133687.10
rows=4 width=256) (actual time=9438.132..29335.008 rows=91 loops=1)
                      ->  Nested Loop  (cost=63.64..133685.78 rows=4
width=320) (actual time=9438.128..29314.640 rows=91 loops=1)
                            ->  Nested Loop  (cost=63.64..133646.56
rows=6 width=384) (actual time=9438.087..29312.490 rows=91 loops=1)
                                  ->  Nested Loop
(cost=63.64..127886.54 rows=2833 width=192) (actual
time=192.451..21060.439 rows=211357 loops=1)
                                        ->  Nested Loop
(cost=63.64..107934.83 rows=13709 width=256) (actual
time=192.367..11591.661 rows=370803 loops=1)
                                              ->  Index Scan using
i_nomen_num on a_nomen n  (cost=0.00..56.39 rows=24 width=128) (actual
time=0.045..0.434 rows=20 loops=1)
                                                    Index Cond:
(((num)::text >= '191'::text) AND ((num)::text < '192'::text))
                                                    Filter:
((num)::text ~~ '191%'::text)
                                              ->  Bitmap Heap Scan on
a_sklad s  (cost=63.64..4480.23 rows=1176 width=256) (actual
time=14.333..565.417 rows=18540 loops=20)
                                                    Recheck Cond:
(s.ids_num = n.ids)
                                                    ->  Bitmap Index
Scan on i_sklad_ids_num  (cost=0.00..63.34 rows=1176 width=0) (actual
time=9.164..9.164 rows=18540 loops=20)
                                                          Index Cond:
(s.ids_num = n.ids)
                                        ->  Index Scan using
i_a_gar_prod_r_ids_a_sklad on a_gar_prod_r gr  (cost=0.00..1.44 rows=1
width=64) (actual time=0.024..0.024 rows=1 loops=370803)
                                              Index Cond:
(gr.ids_a_sklad = s.ids)
                                              Filter: (gr.sernum <> 'ok'::text)
                                  ->  Index Scan using a_doc_pkey on
a_doc d  (cost=0.00..2.02 rows=1 width=256) (actual time=0.038..0.038
rows=0 loops=211357)
                                        Index Cond: (d.ids = s.ids_doc)
                                        Filter: ((d.date_op >= 12320)
AND (d.date_op <= 12362) AND (d.op = 1))
                            ->  Index Scan using a_klienti_pkey on
a_klienti kl  (cost=0.00..6.53 rows=1 width=64) (actual
time=0.021..0.022 rows=1 loops=91)
                                  Index Cond: (kl.ids = d.ids_ko)
                      ->  Index Scan using a_slujiteli_pkey on
a_slujiteli sl  (cost=0.00..0.32 rows=1 width=64) (actual
time=0.222..0.222 rows=0 loops=91)
                            Index Cond: (sl.ids = d.ids_slu_ka)
                ->  Index Scan using a_location_pkey on a_location l
(cost=0.00..0.27 rows=1 width=64) (actual time=0.220..0.220 rows=1
loops=91)
                      Index Cond: (l.ids = s.ids_sklad)
          ->  Index Scan using a_nom_gr_pkey on a_nom_gr nmgr
(cost=0.00..2.77 rows=1 width=64) (actual time=0.083..0.084 rows=1
loops=91)
                Index Cond: (nmgr.ids = n.ids_grupa)
    ->  Index Scan using a_slujiteli_pkey on a_slujiteli slu
(cost=0.00..8.27 rows=1 width=64) (actual time=0.731..0.732 rows=1
loops=91)
          Index Cond: (slu.ids = d.ids_slu_targ)
  Total runtime: 29430.170 ms



After this I wait a little time ( ~30 min) and all works bad again.
I think it is related to cache or not ?

Can I disable using index of n.num field for this query onli ( I know
it is wrong direction, but I have no idea how to solve this situaion) ?

Regards,
Ivan.





-------------------------------------

3.5 Mbps Сателитен достъп до Интернет
навсякъде в България
www.tooway.bg
  http://www.tooway.bg/