PG_DATABASE Sistem Tablosu

Giriş

Tüm veri tabanı isimleri bu tablodadır

Örnek

Şöyle yaparız

SELECT datname FROM pg_database WHERE datname LIKE '${inpDBPattern}' 
ORDER BY 1 OFFSET ${inpOffset} LIMIT ${inpLimit}

PG_STAT_DATABASE Sistem Tablosu

Giriş

Açıklaması şöyle

... you can use the pg_stat_database view to retrieve information on database-level activity, such as the number of connections, transactions, and queries per second

Bu tablosu temizlemek için şöyle yaparız

SELECT pg_stat_reset(); 

PG_SETTINGS Sistem Tablosu

Örnek

Şöyle yaparız

SELECT name, setting
FROM pg_settings
WHERE name = ‘effective_io_concurrency’;

Örnek

Şöyle yaparız

SELECT
  name,
  setting,
  category,
  short_desc
FROM pg_settings
WHERE name LIKE '%autovacuum%'

Örnek

Şöyle yaparız

SELECT name, setting, boot_val FROM pg_settings WHERE name LIKE "autovacuum%";

PG_STAT_BGWRITER Sistem Tablosu - View To Monitor The Overall I/O Activity Of The Database

Örnek

Şöyle yaparız

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, 
  maxwritten_clean, buffers_backend, buffers_alloc
FROM pg_stat_bgwriter;

Açıklaması şöyle

This query returns a single row with several columns that provide information about the background writer process. The columns show the number of checkpoints that have been performed, the number of buffers written during checkpoints, the number of buffers cleaned by the background writer, the maximum number of buffers written in a single clean pass, and the number of buffers allocated and used by backends.

PG_STATIO_USER_TABLES Sistem Tablosu - View to monitor disk I/O Activity For Individual Tables

Örnek

Şöyle yaparız

SELECT relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables;

Açıklaması şöyle

This query returns a row for each table in the database, with columns for the number of blocks read and hit for both the table and its indexes. A high ratio of “hit” to “read” indicates that data is being retrieved from the cache, which is typically a good sign of good performance.

LOWER - Case-Insensitive İşlem İçin Kullanılabilir

Giriş

Açıklaması şöyle

To perform case-insensitive queries, use ILIKE, regular expressions, or the lower() function on both sides of the comparison operator. If lower() is used frequently, the column should be indexed using the case_insensitive collation since otherwise, the index will not be used. 

LOWER ve Index İlişkisi

Açıklaması şöyle

When the Lower function is used, the indexes on the relevant column become unusable. Each search will be done as a “sequential scan.” This will cause performance problems as the number of rows increases. “Expressional Index” is recommended as a solution. For details, see https://www.postgresql.org/docs/9.1/indexes-expressional.html.

Örnek - Unique Index With LOWER

Açıklaması şöyle

For example, a common way to do case-insensitive comparisons is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index if one has been defined on the result of the lower(col1) function:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

If we were to declare this index UNIQUE, it would prevent creation of rows whose col1 values differ only in case, as well as rows whose col1 values are actually identical. Thus, indexes on expressions can be used to enforce constraints that are not definable as simple unique constraints.

Örnek - Case Insensitive Unique Index With Collation

Açıklaması şöyle

If lower() is used frequently, the column should be indexed using the case_insensitive collation since otherwise, the index will not be used. 

Şöyle yaparız

/*See https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC */
CREATE COLLATION case_insensitive
(provider = icu, locale='und-u-ks-level2', deterministic=false);

Sonra şöyle yaparız

CREATE UNIQUE INDEX users_email_unique ON users(email COLLATE case_insensitive);

İndeks'in kullanıldığını görmek için şöyle yaparız

EXPLAIN
SELECT * FROM users
WHERE lower(email) = lower('Seed-system-user')
AND deleted_at IS NULL;

Çıktı şöyle olmalı

-> Bitmap Index Scan on users_email_unique

Kullanım

Örnek

Şöyle yaparız

SELECT * FROM test1 WHERE lower(col1) = lower('value');

Bitmap Index Scan ve Bitmap Heap Scan

Giriş

Açıklaması şöyle

.. a Bitmap Heap Scan always works in tandem with Bitmap Index Scan to speed things up.

Açıklaması şöyle

Bitmap Index Scan scans the index first to create a bitmap of which pages need to be fetched, and Bitmap Heap Scan then uses the bitmap to fetch the data from the pages.

Neden Lazım

Açıklaması şöyle. Yani elimizde bir indeks varsa ancak sıralı değilse - örneğin indeks text içinse - kullanılır.

.... index scans cause random I/O if there is no ordering to the rows (name is text content). This is costly in rotational hard drives. To solve this, the Planner takes a two-stage approach. The Bitmap Index Scan constructs a data structure called a bitmap from the index present and represents the approximate location of pages in the disk and feeds it to the parent node, i.e., Bitmap Heap Scan, which then uses that to fetch the pages/rows.

Örnek

Şöyle yaparız. Burada name alanı için indeks yaratılıyor

CREATE INDEX name_str_idx ON fake_data USING BTREE(name);

Sorgu için şöyle yaparız

EXPLAIN ANALYZE SELECT * FROM fake_data WHERE 
 fake_data.name = 'David';

Örnek

Şöyle yaparız

CREATE INDEX idx weather_type ON weather(event_type); 

Bitmap Heap Scan on weather (cost=4.36..37.56 rows=11 width=622) (actual time=0.500..2.300 rows=11 loops=1)
Recheck Cond: ((event type)::text = 'Winter Storm'::text)
Heap Blocks: exact=6
-> Bitmap Index Scan on idx weather type (cost=0.00..4.36 rows=11] width =0) (actual time=0.400..0.400 rows=11 loops=1)
    Index Cond: ((event type)::text = 'Winter Storm'::text)
Planning Time: 23.300 ms
Execution Time: 7.200 ms
(7 rows)

Örnek - Bitmap Or

Eğer iki tane indeks varsa kullanılır. Elimizde şu anda id ve name için iki tane indeks var. 

Sorgu için şöyle yaparız

EXPLAIN ANALYZE SELECT * FROM fake_data WHERE 
 fake_data.id = 1000 OR fake_data.name = 'David';

Örnek - Bitmap And

Eğer iki tane indeks varsa kullanılır. Elimizde şu anda id ve name için iki tane indeks var. 

Sorgu için şöyle yaparız

EXPLAIN ANALYZE SELECT * FROM fake_data WHERE 
 fake_data.id <= 1000 OR fake_data.name = 'David';

5. Parallel Scans

Açıklaması şöyle

Sequential Scans are the slowest of all the plans we have seen so far because there is nothing to optimize there. The Planner goes over the data sequentially and tries to find the result. PostgreSQL optimizes this further by adding parallelism in the queries.

NESTED LOOP Scan

Giriş

Scan çeşitleri tek bir tablo varsa kullanılır. Eğer bir işlemde - örneğin JOIN - iki tane tablo varsa dışarıda bir NESTED LOOP içeride de Scan çeşitlerinden birkaç tanesi kullanılır

Örnek

Elimizde şöyle bir SQL olsun. Burada customer_id = 5 olan müşterini siparişleri sayılıyor

EXPLAIN ANALYZE SELECT count(*)
FROM orders AS o
 JOIN order_items as oi
  ON o.order_id = oi.order_item_order_id
WHERE o.order_customer_id = 5;

Çıktısı şöyle. Burada dışarıdaki döngü için Bitmap Index Scan ve Bitmap Heap Scan kullanılıyor. İçerideki döngü için Index Only Scan kullanılıyor

|QUERY PLAN                                                                                                                                                         |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Aggregate  (cost=53.71..53.72 rows=1 width=8) (actual time=2.434..2.435 rows=1 loops=1)                                                                            |
|  ->  Nested Loop  (cost=4.76..53.67 rows=15 width=0) (actual time=1.872..2.428 rows=7 loops=1)                                                                    |
|        ->  Bitmap Heap Scan on orders o  (cost=4.34..26.49 rows=6 width=4) (actual time=1.034..1.315 rows=4 loops=1)                                              |
|              Recheck Cond: (order_customer_id = 5)                                                                                                                |
|              Heap Blocks: exact=4                                                                                                                                 |
|              ->  Bitmap Index Scan on orders_order_customer_id_idx  (cost=0.00..4.34 rows=6 width=0) (actual time=0.696..0.696 rows=4 loops=1)                    |
|                    Index Cond: (order_customer_id = 5)                                                                                                            |
|        ->  Index Only Scan using order_items_order_item_order_id_idx on order_items oi  (cost=0.42..4.49 rows=4 width=4) (actual time=0.275..0.276 rows=2 loops=4)|
|              Index Cond: (order_item_order_id = o.order_id)                                                                                                       |
|              Heap Fetches: 0                                                                                                                                      |
|Planning Time: 5.706 ms                                                                                                                                            |
|Execution Time: 2.565 ms      

Örnek - GroupAggregate  

GROUP BY varsa kullanılır Elimizde şöyle bir SQL olsun. Siparişleri gruplar ve toplam ciroyu verir.

EXPLAIN
SELECT o.*,
    round(sum(oi.order_item_subtotal)::numeric, 2) as revenue
FROM orders as o
 JOIN order_items as oi
  ON o.order_id = oi.order_item_order_id
WHERE o.order_id = 2
GROUP BY o.order_id, o.order_date, o.order_customer_id, o.order_status;

Çıktı şöyle

|order_id|order_date             |order_customer_id|order_status   |revenue|
|--------|-----------------------|-----------------|---------------|-------|
|2       |2013-07-25 00:00:00.000|256              |PENDING_PAYMENT|579.98 |

Plan şöyle. Bu sefer Nested Loop dışında bir tane de GroupAggregate var

|QUERY PLAN                                                                             |
|---------------------------------------------------------------------------------------|
|GroupAggregate  (cost=0.29..3427.86 rows=1 width=58)                                   |
|  Group Key: o.order_id                                                                |
|  ->  Nested Loop  (cost=0.29..3427.82 rows=4 width=34)                                |
|        ->  Index Scan using orders_pkey on orders o  (cost=0.29..8.31 rows=1 width=26)|
|              Index Cond: (order_id = 2)                                               |
|        ->  Seq Scan on order_items oi  (cost=0.00..3419.47 rows=4 width=12)           |
|              Filter: (order_item_order_id = 2)            

pg_ctl komutu

Giriş

Açıklaması şöyle

Ubuntu/Debian packages for Postgres have their own layer on top of initdb and pg_ctl to control multiple instances and the integration with systemd.

restart

Örnek

Şöyle yaparız

pg_ctl -D /pgsql/data/replica/ restart