Sequential Scan

Giriş

Not : Parallel Sequential Scan aynı Sequential Scan gibidir, sadece birden fazla worker kullanılır.

Açıklaması şöyle. Full Table Scan olarak da bilinir. Tabloda index yoksa mecburen Full Table Scan yapılır. 

This is the simplest of them all; if there is no index or a lesser number of rows, then the Planner resorts to scanning all of the rows present. You would/should never encounter Sequential Scans in a typical production system since they are very slow and get slower as the data increases. There are exceptions when the table is too small where an index is pointless and Sequential Scans are fast enough that you don’t have to worry.

Açıklaması şöyle

One thing to understand is that there are certain kinds of queries, such as Count, Avg and other aggregate queries, which always result in Sequential Scan because they have to scan the entirety of the table anyway for the result. 

Örnek

Şöyle yaparız. Burada Seq Scan metninden de görüldüğü gibi sequential scan kullanılıyor

explain analyze SELECT EXISTS (
  SELECT
    1
  FROM
    balances_snapshots
  WHERE
    total_balance = double precision 'NaN'
  LIMIT 1
) as exists;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4.75..4.76 rows=1 width=1) (actual time=237365.680..237365.681 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on balances_snapshots  (cost=0.00..9257326.32 rows=1948181 width=0) (actual time=237365.675..237365.676 rows=0 loops=1)
           Filter: ((total_balance)::double precision = 'NaN'::double precision)
           Rows Removed by Filter: 389636289
 Planning Time: 23.985 ms
 Execution Time: 237365.719 ms
(7 rows)

Sebebi ise total_balance alanının "numeric" olması. Ancak sorguda "double precision" yani double olarak arama yapılıyor

Örnek

Şöyle yaparız. Burada event_type için indeks yok

EXPLAIN ANALYZE SELECT * FROM weather WHERE event_type = 'Winter Storm'; 

Seq Scan on weather (cost=0.00..115.00 rows=11] width=622) (actual time=0.100..19.800 rows=11] loops=1)
  Filter: ((event type)::text = 'Winter Storm'::text)
  Rows Removed by Filter: 1189
Planning Time: 30.000 ms
Execution Time: 20.800 ms
(5 rows)

Bir indeks yaratırsak Sequential Scan yapmaz

CREATE INDEX idx weather_type ON weather(event_type); 

Eğer where koşulunda iki tane sütun olsaydı indekste iki tane alan olmalıydı ve alanların sırası önemli. Şöyle yaparız 

CREATE INDEX idx_storm_crop ON weather (event_type, damage_crops); 
SELECT * FROM weather WHERE event_type = 'Winter Storm' AND damage_crops > 0;        

Eğer GROUP BY kullanırsak yine Sequential Scans yapar 

SELECT state FROM weather 
WHERE event_type = 'Winter Storm’ AND damage_crops > O
GROUP BY state; 

Açıklaması şöyle

The planner will need to load all of the data to the memory and sort all of the data by state, then for each state it’ll need to filter out by our WHERE statement.

Bu sefer indeki büyütmek gerekiyor. Şöyle yaparız 

CREATE INDEX idx_storm_crop ON weather (state, event_type, damage_crops);