28 Ekim 2021 Perşembe

EXPLAIN ANALYZE - Cümleyi Çalıştırır

Giriş
SQL cümlesi için kullanılan "Query Plan" bilgisini gösterir. Açıklaması şöyle
There are several node types in PostgreSQL query plans,
Scans.
Joins.
Sort.
Aggregates etc.,
Explain vs Explain Analyze
Explain için söz dizimi şöyle
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM your_table WHERE condition;
Açıklaması şöyle. Yani EXPLAIN ANALYZE cümleyi çalıştırır
You can use the EXPLAIN and EXPLAIN ANALYZE commands to view the query plan and see if it makes sense (i.e. by running EXPLAIN ANALYZE <query_statement>).

Note that EXPLAIN is a read-only DB operation but EXPLAIN ANALYZE isn’t! Be very careful not to use EXPLAIN ANALYZE on an UPDATE or DELETE statement, as it will actually execute the query and modify the database.

Scan Çeşitleri
Bazı çeşitler şöyle
1. Sequential Scans
2. Index Scans
3. Index Only Scans
4. Bitmap Heap Scan & Bitmap Index Scan
5. Parallel Scans

1. Sequential Scans
Sequential Scans yazısına taşıdım

2. Index Scans
Eğer tabloda bir sütun için indeks varsa kullanılır. Index genellikle where koşulundaki sütuna konulur

Where koşulu Sırası
Açıklaması şöyle. Yani (tenantId, status) şeklinde index olsa bile ikinci sırada belirtilen where koşulu bunu kullanmayabiliyor. (status, tenantId) şeklinde yeni bir index yaratılabilir ama bence en kolayı sorguyu değiştirmek
Two of the queries had the following WHERE clause:
WHERE "tenantId" = 'some_tenant_id' AND "status" != 'dismissed'.
The other two had the conditions in the opposite order:
WHERE "status" != 'dismissed' AND "tenantId" = 'some_tenant_id'.

Postgres may choose different execution plans depending on the order of the conditions in the WHERE clause, so it treated these as distinct queries. As mentioned earlier, I added a multi-column index on the (tenantId, status) columns in the Notes table, but this only improved the performance of the first two queries. The other two queries, which had the opposite order of conditions in the WHERE clause, were unable to benefit from this index.

Örnek
Şöyle yaparız. Burada id alanı için indeks yaratılıyor
CREATE INDEX id_idx ON fake_data USING BTREE(id);
Sorgu için şöyle yaparız
EXPLAIN ANALYZE SELECT * FROM fake_data WHERE 
 fake_data.id = 1000;
Örnek
Şöyle yaparız
SELECT * FROM songs WHERE (artists_id = 1 AND album_id = 1);
Çıktısı şöyle
Index Scan using songs_artists_id_album_id_index on songs  
(cost=0.28..6.05 rows=1 width=159) (actual time=5.555..5.562 rows=10 loops=1)
   Index Cond: ((artists_id = 1) AND (album_id = 1))
 Planning Time: 311.482 ms
 Execution Time: 9.266 ms
(4 rows)
Açıklaması şöylee
(cost=0.28..6.05 rows=1 width=159) refers to the planner's estimations while (actual time=5.555..5.562 rows=10 loops=1) refers to the actual results of the executing the plan. The planner estimated 1 row would be returned, but there were actually 10.

The planner calculated its row estimate by first taking the total number of Songs (1000), then considering the artists_id filter. 10% of Songs have artists_id = 1 so that leaves 100 Songs. Next it considers the album_id filter. 1% of Songs have album_id = 1, so it's left with 1 Song.

The key piece of information Postgres is missing is that artist_id and album_id are strongly correlated. In fact, knowing the album_iduniquely determines the artist_id. Had Postgres known about this, it could have used only the album_id = 1 filter in its estimation and come up with the correct result of 10 Songs.

3. Index Only Scans
Index Only Scans yazısına taşıdım

4. Bitmap Index Scan ve Bitmap Heap Scan


Hiç yorum yok:

Yorum Gönder