Weird index scan

From: "Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Weird index scan
Date: 2005-03-30 12:37:18
Message-ID: A66A11DBF5525341AEF6B8DE39CDE770088066@black.aprote.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-performance by date

  From Date Subject
Next Message Alvaro Herrera 2005-03-30 15:19:03 Re: VACUUM on duplicate DB gives FSM and total pages discrepancies
Previous Message Marc Burgauer 2005-03-30 11:07:29 Reading recommendations