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

Bitmap Index Scan ve Bitmap Heap Scan yazısına taşıdım