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);