Weird index scan

Поиск
Список
Период
Сортировка
От Tambet Matiisen
Тема Weird index scan
Дата
Msg-id A66A11DBF5525341AEF6B8DE39CDE770088066@black.aprote.com
обсуждение исходный текст
Список pgsql-performance
I'm getting weird results for one of my queries. The actual time of this
index scan doesn't make any sense:

->  Index Scan using dok_dok_fk_i on dokumendid a  (cost=0.00..566.24
rows=184 width=8) (actual time=0.170..420806.563 rows=1 loops=1)

dok_dok_fk_i is index on dokumendid(dok_dok_id). Currently it contains
mostly NULLs:

pos1=# select dok_dok_id, count(1) from dokumendid group by dok_dok_id;
 dok_dok_id | count
------------+-------
            | 11423
       8034 |    76
(2 rows)

If I drop the index, seq scan + sort is used instead and everything is
fast again.

The PostgreSQL version:

pos1=# select version();
                                               version
------------------------------------------------------------------------
------------------------------
 PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.4 (Debian 1:3.3.4-9)
(1 row)

The full EXPLAIN ANALYZE output:

pos1=# explain analyze select * from v_inventuuri_vahed_kaubagrupiti;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------
 Subquery Scan v_inventuuri_vahed_kaubagrupiti  (cost=50896.04..50896.61
rows=46 width=128) (actual time=437007.670..437007.817 rows=45 loops=1)
   ->  Sort  (cost=50896.04..50896.15 rows=46 width=42) (actual
time=437007.664..437007.692 rows=45 loops=1)
         Sort Key: (COALESCE(sum(ir.summa_kmta), 0::numeric))::raha
         ->  HashAggregate  (cost=50893.85..50894.77 rows=46 width=42)
(actual time=437007.229..437007.488 rows=45 loops=1)
               ->  Hash Join  (cost=5533.44..50807.93 rows=5728
width=42) (actual time=436226.533..436877.499 rows=16271 loops=1)
                     Hash Cond: ("outer".kau_kau_id = "inner".kau_id)
                     ->  Merge Right Join  (cost=4759.52..49858.92
rows=15696 width=26) (actual time=436117.333..436600.653 rows=16271
loops=1)
                           Merge Cond: (("outer".dok_dok_id =
"inner".dok_id) AND ("outer".kau_kau_id = "inner".kau_kau_id))
                           ->  Index Scan using dor_dok_kau_i on
dokumentide_read ar  (cost=0.00..42789.44 rows=480962 width=19) (actual
time=0.023..7873.117 rows=205879 loops=1)
                           ->  Sort  (cost=4759.52..4798.76 rows=15696
width=19) (actual time=428381.719..428392.204 rows=16271 loops=1)
                                 Sort Key: a.dok_id, ir.kau_kau_id
                                 ->  Merge Left Join
(cost=0.00..3665.65 rows=15696 width=19) (actual time=0.245..428279.595
rows=16258 loops=1)
                                       Merge Cond: ("outer".dok_id =
"inner".dok_dok_id)
                                       ->  Nested Loop
(cost=0.00..3620.23 rows=15696 width=19) (actual time=0.063..7243.529
rows=16258 loops=1)
                                             ->  Index Scan using dok_pk
on dokumendid i  (cost=0.00..3.73 rows=1 width=4) (actual
time=0.030..0.035 rows=1 loops=1)
                                                   Index Cond: (dok_id =
8034)
                                                   Filter: (tyyp =
'IN'::bpchar)
                                             ->  Index Scan using
dor_dok_fk_i on dokumentide_read ir  (cost=0.00..3459.55 rows=15696
width=19) (actual time=0.023..7150.257 rows=16258 loops=1)
                                                   Index Cond: (8034 =
dok_dok_id)
                                       ->  Index Scan using dok_dok_fk_i
on dokumendid a  (cost=0.00..566.24 rows=184 width=8) (actual
time=0.170..420806.563 rows=1 loops=1)
                                             Filter: (tyyp =
'IA'::bpchar)
                     ->  Hash  (cost=757.71..757.71 rows=6487 width=24)
(actual time=109.178..109.178 rows=0 loops=1)
                           ->  Hash Join  (cost=15.56..757.71 rows=6487
width=24) (actual time=1.787..85.554 rows=17752 loops=1)
                                 Hash Cond: ("outer".kag_kag_id =
"inner".a_kag_id)
                                 ->  Seq Scan on kaubad k
(cost=0.00..588.52 rows=17752 width=8) (actual time=0.005..30.952
rows=17752 loops=1)
                                 ->  Hash  (cost=15.35..15.35 rows=83
width=24) (actual time=1.770..1.770 rows=0 loops=1)
                                       ->  Hash Join  (cost=5.39..15.35
rows=83 width=24) (actual time=0.276..1.491 rows=227 loops=1)
                                             Hash Cond:
("outer".y_kag_id = "inner".kag_id)
                                             ->  Seq Scan on
kaubagruppide_kaubagrupid gg  (cost=0.00..7.09 rows=409 width=8) (actual
time=0.004..0.405 rows=409 loops=1)
                                             ->  Hash  (cost=5.27..5.27
rows=46 width=20) (actual time=0.259..0.259 rows=0 loops=1)
                                                   ->  Seq Scan on
kaubagrupid g  (cost=0.00..5.27 rows=46 width=20) (actual
time=0.010..0.206 rows=46 loops=1)
                                                         Filter:
(kag_kag_id IS NULL)
 Total runtime: 437011.532 ms
(33 rows)

  Tambet

В списке pgsql-performance по дате отправления:

Предыдущее
От: Marc Burgauer
Дата:
Сообщение: Reading recommendations
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: VACUUM on duplicate DB gives FSM and total pages discrepancies