Warum "Sec Scan" und nicht "Index Only Scan"

Hallo!

Um meine Test-Tabelle und den Index zu erstellen (ich hab postgres 9.6.1 auf Win10, alles auf default):

CREATE TABLE meineTabelle (spalte1 bigint, spalte1_Index bigint, spalte2 text); 
INSERT INTO meineTabelle SELECT i,i, md5(random()::text) FROM generate_series(1, 1000000) AS i;
create index meinindex on meinetabelle (Spalte1_Index);

Dieses select verwendet nur Daten, die im Index vorkommen (Spalte spalte1_index):

select spalte1_index from meinetabelle where spalte1_index > 990 ;

Trotzdem entscheidet sich postgres für Sec Scan. Warum?:

postgres=# explain analyse verbose select spalte1_index from meinetabelle where spalte1_index > 990 ;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on public.meinetabelle  (cost=0.00..22810.00 rows=999039 width=8) (actual time=0.156..1050.511 rows=999010 loops=1)
   Output: spalte1_index
   Filter: (meinetabelle.spalte1_index > 990)
   Rows Removed by Filter: 990
 Planning time: 0.191 ms
 Execution time: 1938.472 ms

Die Kosten für einen Index Only Scan sind: cost=0.42…25636.28 ( erzwungen durch: set enable_seqscan=off; )
Erst wenn die Zeilenanzahl vom Result Set sehr klein wird, schwenkt postgres auf Index Only Scan um (where-Klausel verändert):

postgres=# explain analyse verbose select spalte1_index from meinetabelle where spalte1_index > 993930 ;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Index Only Scan using meinindex on public.meinetabelle  (cost=0.42..160.59 rows=5495 width=8) (actual time=0.357..6.138 rows=6070 loops=1)
   Output: spalte1_index
   Index Cond: (meinetabelle.spalte1_index > 993930)
   Heap Fetches: 0
 Planning time: 0.206 ms
 Execution time: 11.718 ms

:confused:

Danke

Also auf meinem Rechner ist der Seq Scan in der Tat schneller als der Index Only Scan:

Seq Scan on stuff.meinetabelle  (cost=0.00..13810.00 rows=999134 width=8) (actual time=0.209..236.114 rows=999010 loops=1)
  Output: spalte1_index
  Filter: (meinetabelle.spalte1_index > 990)
  Rows Removed by Filter: 990
Planning time: 0.155 ms
Execution time: 271.303 ms

und:

Index Only Scan using meinindex on stuff.meinetabelle  (cost=0.42..18913.53 rows=999134 width=8) (actual time=0.021..359.204 rows=999010 loops=1)
  Output: spalte1_index
  Index Cond: (meinetabelle.spalte1_index > 990)
  Heap Fetches: 999010
Planning time: 0.159 ms
Execution time: 398.949 ms

beim lesen aus einem Index kommen noch cpu_index_tuple_cost dazu, default 0.005. Macht hier schon mal ca. 5000 aus. Und: cpu_tuple_cost mit 0.01. Und: lesen aus der Tabelle ist sequentiell mit Kosten default 1, während Index-lesen Kosten von default 4 hat. Die Tabelle hat (bei mir) 10310 pages, der Index 2745. Wäre Deine Tabelle breiter, würde der Index eher greifen.

Aber interessant allemal :wink:

bei mir auch :wink:

Wieso hast du Zugriffe auf den Heap:

Heap Fetches: 999010

Wie sieht dein select aus? die heap-zugriffe können durch nur zustande kommen, wenn etwas abgefragt wird, dass nicht im Index?
:confused:

Das war das Statement aus Deinem Beispiel:

select spalte1_index 
from meinetabelle 
where spalte1_index > 990;

Hab’s jetzt nochmal laufen lassen und nach dem Anlegen der Tabelle ein vacuum analyze meinetabelle gemacht anstatt nur einem analyze meinetabelle und dann wird auf einmal der Index Scan bevorzugt. Der ist aber letztendlich genauso schnell wie der Seq Scan:

Index Only Scan using meinindex on stuff.meinetabelle  (cost=0.42..8610.53 rows=999023 width=8) (actual time=0.536..181.473 rows=999010 loops=1)
  Output: spalte1_index
  Index Cond: (meinetabelle.spalte1_index > 990)
  Heap Fetches: 0
  Buffers: shared hit=2737
Planning time: 4.755 ms
Execution time: 228.259 ms

bzw:

Seq Scan on stuff.meinetabelle  (cost=0.00..13810.00 rows=999023 width=8) (actual time=0.189..221.999 rows=999010 loops=1)
  Output: spalte1_index
  Filter: (meinetabelle.spalte1_index > 990)
  Rows Removed by Filter: 990
  Buffers: shared hit=10310
Planning time: 0.156 ms
Execution time: 269.815 ms

Mit Heap ist hier wohl auch der Index mit gemeint. Allerdings ist die Frage berechtigt.

Hrm. Da gibt es noch mehr zu beachten, die Visibility Map. Der Index liefert die Werte, hat aber keine Informationen über die Sichtbarkeit. Daher wird in der VM noch geschaut, ob die pages sichtbar sind. Wenn relallvisible nicht gesetzt ist muß im Heap geschaut werden.

postgres=# create table foo (id serial primary key);
CREATE TABLE
postgres=# insert into foo select nextval('foo_id_seq'::regclass) from generate_series(1,1000);
INSERT 0 1000
postgres=# analyse;
ANALYZE
postgres=# explain (analyze, verbose) select id from foo where id = 500;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on public.foo  (cost=0.28..8.29 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)
   Output: id
   Index Cond: (foo.id = 500)
   Heap Fetches: 1
 Planning time: 0.258 ms
 Execution time: 0.050 ms
(6 Zeilen)

postgres=# update foo set id = 500 where id = 500;
UPDATE 1
postgres=# explain (analyze, verbose) select id from foo where id = 500;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on public.foo  (cost=0.28..8.29 rows=1 width=4) (actual time=0.029..0.067 rows=1 loops=1)
   Output: id
   Index Cond: (foo.id = 500)
   Heap Fetches: 2
 Planning time: 0.100 ms
 Execution time: 0.143 ms
(6 Zeilen)

Nach dem Update sind 2 Fetches nötig, der alte und der neue Record, nur der letzt ist sichtbar, hat eine ctid am ende:

postgres=# select ctid, * from foo where id between 498 and 502;
  ctid  | id  
--------+-----
 (2,46) | 498
 (2,47) | 499
 (4,97) | 500
 (2,49) | 501
 (2,50) | 502
(5 Zeilen)

Hier die VM (Visibility Map)

postgres=# create extension pg_visibility;
CREATE EXTENSION
postgres=# select * from pg_visibility_map('foo');
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
(5 Zeilen)

nach einem vacuum freeze:

postgres=# vacuum freeze foo;
VACUUM
postgres=# select * from pg_visibility_map('foo');
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | t
     1 | t           | t
     2 | t           | t
     3 | t           | t
     4 | t           | t
(5 Zeilen)

Und nun, tätää:

postgres=# explain (analyze, verbose) select id from foo where id = 500;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on public.foo  (cost=0.28..4.29 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
   Output: id
   Index Cond: (foo.id = 500)
   Heap Fetches: 0
 Planning time: 0.116 ms
 Execution time: 0.040 ms
(6 Zeilen)

Also: ein Index-Only-Scan ist quasi lediglich der Versuch, die Daten vollständig aus dem Index zu holen, dieser Versuch kann mehr oder weniger auch scheitern :wink:

Danke für die Antworten.

1/3
Mir ist jetzt erst aufgefallen, dass wenn ich set enable_seqscan=off; setze, ich 999010 Heap-Zugriffe habe

postgres=# explain analyse  select spalte1_index from meinetabelle where spalte1_index > 990 ;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using meinindex on meinetabelle  (cost=0.42..38760.10 rows=999067 width=8) (actual time=0.040..1260.386 rows=999010 loops=1)
   Index Cond: (spalte1_index > 990)
   Heap Fetches: 999010

Darum macht der Planer gleich einen Sec Scan durch den Heap, also der eigentlichen Datentabelle, richtig?

ja.

2 / 3

Hab’s jetzt nochmal laufen lassen und nach dem Anlegen der Tabelle ein vacuum analyze meinetabelle gemacht anstatt nur einem analyze meinetabelle und dann wird auf einmal der Index Scan bevorzugt.

Kann ich leider nicht reproduzieren.
Ich hab auch Variationen wie vacuum full analyse meinetabelle probiert …

3 / 3

Zu Virtual Map:

https://www.postgresql.org/docs/devel/static/storage-vm.html

Es geht also darum, ob Zeilen gerade bearbeitet werden bzw gelockt sind. Und der Index nicht weiß, ob seine Spaltenwerte aktuell sind und darum im Heap nachsehen muss?

Visibility map bits are only set by vacuum, but are cleared by any data-modifying operations on a page.
… This information can also be used by > > index-only scans > to answer queries using only the index tuple.

Visibility map bits are only set by vacuum
Also wenn gesetzt, dann ist der Index aktuell und der Index-Only-Scan ist ein echter Index-Only-Scan ohne Heap-Zugriffe?



relallvisible meiner Tabelle ist 0:

postgres=# vacuum full analyse;
VACUUM
Zeit: 7975,109 ms
postgres=# select relallvisible from pg_class WHERE relname='meinetabelle';
 relallvisible
---------------
             0

Da relallvisible 0, muss der Index-Only-Scan im Heap nachsehen?

ja

danke, dann hab ich alles richtig verstanden.