Skip to content

Instantly share code, notes, and snippets.

@DBalashov

DBalashov/postgres_useful_queries.md

Created April 18, 2024 12:02
Show Gist options
  • Save DBalashov/35abec3497b91421f21c4f75a8ce0251 to your computer and use it in GitHub Desktop.
Save DBalashov/35abec3497b91421f21c4f75a8ce0251 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres_useful_queries.md

Другие интересные штуки тут: https://t.me/notes_of_programmer

Текущие выполняемые запросы

select pid, user, client_addr, query_start, backend_type, substr(query, 0, 256) as query
from pg_stat_activity
where query_start is not null
  and client_addr is not null;

Пример вывода:

pid user client_addr query_start backend_type query
228778 postgres 172.18.0.2 2024-04-18 11:16:43.864481+00 client backend SELECT d."State", d."IDDEV"
FROM "DeviceApp" AS d
WHERE d."ID" = $1 AND d."Type" = 1
LIMIT 1
228325 postgres 172.18.0.2 2024-04-18 11:16:43.863796+00 client backend SELECT d."State", d."IDDEV"
FROM "DeviceApp" AS d
WHERE d."ID" = $1 AND d."Type" = 1
LIMIT 1
228779 postgres 172.18.0.2 2024-04-18 11:16:43.862443+00 client backend SELECT d."State", d."IDDEV"
FROM "DeviceApp" AS d
WHERE d."ID" = $1 AND d."Type" = 1
LIMIT 1
228792 postgres 172.18.0.2 2024-04-18 11:16:43.863083+00 client backend SELECT d."State", d."IDDEV"
FROM "DeviceApp" AS d
WHERE d."ID" = $1 AND d."Type" = 1
LIMIT 1

Запросы, выполняемые более 1 секунды

select pid,
       user,
       pg_stat_activity.query_start,
       now() - pg_stat_activity.query_start as query_time,
       query,
       state,
       wait_event_type,
       wait_event
from pg_stat_activity
where (now() - pg_stat_activity.query_start) > interval '1 sec';

Пример вывода:

pid user query_start query_time query state wait_event_type wait_event
228599 postgres 2024-04-18 11:02:45.482557+00 00:13:23.551514 SELECT * from pg_stat_activity; idle Client ClientRead

Таблицы с % попадания в кэш с обращении

select relname                                                               as "relation",
       heap_blks_read                                                        as heap_read,
       heap_blks_hit                                                         as heap_hit,
       ((heap_blks_hit * 100) / nullif((heap_blks_hit | heap_blks_read), 0)) as ratio
from pg_statio_user_tables;

Пример вывода:

relation heap_read heap_hit ratio
VersionInfo 4 123 96
SystemSetting 4 632 99
CourseHistory 172 182472 99
Org 4 1868 99
User 4 153475404 99
UserOnline 2 232 99
DeviceApp 12 222306882 99

Размеры таблиц (включая индексы)

select relname                                       as "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) as "total_size"
from pg_class as C
         left join pg_namespace as N on (N.oid = C.relnamespace)
where nspname not in ('pg_catalog', 'information_schema')
  and C.relkind <> 'i'
  and nspname = 'public'
order by pg_total_relation_size(C.oid) desc;

Пример вывода:

relation total_size
DeviceCommandStage 2496 kB
CourseHistory 1280 kB
DocumentDoc 312 kB
StoredFile 232 kB
DeviceCommand 224 kB

Размеры индексов

select relname                                 as "relation",
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"
from pg_class C
         left join pg_namespace N ON (N.oid = C.relnamespace)
where nspname not in ('pg_catalog', 'information_schema')
  and C.relkind = 'i'
  and nspname = 'public'
order by pg_relation_size(C.oid) desc

Пример вывода:

relation size
DeviceCommandStage 2280 kB
CourseHistory 664 kB
IX_CourseHistory 408 kB
DocumentDoc 192 kB
PK_CourseHistory 176 kB
StoredFile 136 kB
PK_DeviceCommandStage 120 kB
DeviceCommand 104 kB

Размер текущей БД

select pg_size_pretty(pg_database_size(current_database()));

Пример вывода:

pg_size_pretty
15 MB

Статистика по чтению индексов (колонка number_of_scans, 0 - бесполезный индекс :)

select t.tablename                                                       as "relation",
       indexname,
       c.reltuples                                                       as num_rows,
       pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text))  as table_size,
       pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) as index_size,
       idx_scan                                                          as number_of_scans
from pg_tables t
         left outer join pg_class c on t.tablename = c.relname
         left outer join
     (select c.relname   AS ctablename,
             ipg.relname AS indexname,
             x.indnatts  AS number_of_columns,
             idx_scan,
             idx_tup_read,
             idx_tup_fetch,
             indexrelname,
             indisunique
      from pg_index x
               join pg_class c on c.oid = x.indrelid
               join pg_class ipg on ipg.oid = x.indexrelid
               join pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid)
         as foo
     on t.tablename = foo.ctablename
where t.schemaname = 'public'
order by 1, 2;

Пример вывода:

relation indexname num_rows table_size index_size number_of_scans
Device IX_Device_Model 7 8192 bytes 8192 bytes 0
Device IX_Device_Name 7 8192 bytes 8192 bytes 0
Device IX_Device_Phone 7 8192 bytes 8192 bytes 0
Device IX_Device_UID 7 8192 bytes 16 kB 0
Device PK_Device 7 8192 bytes 16 kB 3098190
DeviceApp IX_DeviceApp_IDDEV 23 8192 bytes 16 kB 3848916
DeviceApp IX_DeviceApp_Token 23 8192 bytes 16 kB 12018
DeviceApp PK_DeviceApp 23 8192 bytes 16 kB 1335247
DeviceAppHistory IX_DeviceAppHistory 138 24 kB 16 kB 16
DeviceAppHistory PK_DeviceAppHistory 138 24 kB 16 kB 0
DeviceAppOnline IX_DeviceStateApp 41 48 kB 40 kB 334435
DeviceAppOnline PK_DeviceAppOnline 41 48 kB 32 kB 211245
DeviceCommand IX_DeviceCommand_Hash 208 104 kB 16 kB 1883
DeviceCommand IX_DeviceCommand_IDDOC 208 104 kB 16 kB 103030627
DeviceCommand IX_DeviceCommand_IDDEV 208 104 kB 16 kB 144384

Размеры и наличие временных файлов

select datname    as "database",
       temp_files as "Temporary files",
       temp_bytes as "Size of temporary files"
from pg_stat_database;

Пример вывода:

database Temporary files Size of temporary files
postgres 0 0
template1 0 0
template0 0 0

Топ 5 самых востребованных таблиц

select schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit
from pg_statio_all_tables
where schemaname = 'public'
order by heap_blks_hit desc limit 5;

Пример вывода:

schemaname relname heap_blks_read heap_blks_hit idx_blks_read idx_blks_hit
public DeviceCommand 21 707387847 25 103458588
public DeviceApp 12 223055953 21 5241850
public User 4 153481225 27 152411014
public Doc 8 119001021 19 75245217
public DeviceAppOnline 29 11652998 22 1108966

Топ 5 самых востребованных индексов

select *
from pg_statio_all_indexes
where schemaname = 'public'
order by idx_blks_hit desc limit 5;

Пример вывода:

relid indexrelid schemaname relname indexrelname idx_blks_read idx_blks_hit
16529 16536 public User PK_User 7 152367168
16615 17003 public DeviceCommand IX_DeviceCommand_IDDOC 5 103092614
16586 16599 public DeviceApp IX_DeviceApp_IDDEV 7 3863789
16570 16574 public Device PK_Device 7 3115820
16586 16592 public DeviceApp PK_DeviceApp 7 1351301

Никогда не использованные индексы (бесполезные :)

select *
from pg_stat_all_indexes
where idx_scan = 0
  and not (relname like 'pg_%')
  and schemaname = 'public';

Пример вывода:

relid indexrelid schemaname relname indexrelname idx_scan idx_tup_read idx_tup_fetch
16390 16393 public VersionInfo UC_Version 0 0 0
16489 16495 public SystemSetting PK_SystemSetting 0 0 0
16529 16543 public User IX_User_EMail 0 0 0
16529 16545 public User IX_User_UID 0 0 0
16529 16546 public User IX_User_Name 0 0 0
16570 16581 public Device IX_Device_UID 0 0 0
16570 16582 public Device IX_Device_Name 0 0 0
16570 16583 public Device IX_Device_Phone 0 0 0
16570 16584 public Device IX_Device_Model 0 0 0
17029 17033 public DeviceCommandFile PK_DeviceCommandFile 0 0 0
17067 17083 public Document IX_Document_IDUSER 0 0 0
17067 17085 public Document IX_Document_Name 0 0 0

Статистика использования индексов при обращении к таблицам

select relname,
       100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
       n_live_tup                             rows_in_table
from pg_stat_user_tables
where (seq_scan + idx_scan) > 0
order by n_live_tup desc;

Пример вывода:

| relname            | percent_of_times_index_used | rows_in_table 
|--------------------|-----------------------------|---------------
| CourseHistory      | 97                          | 7114          
| DeviceCommandStage | 77                          | 4124          
| Doc                | 67                          | 1711          
| StoredFile         | 71                          | 391           
| DeviceCommand      | 57                          | 212           
| UserAction         | 78                          | 84            
| VersionInfo        | 0                           | 44            
| DeviceApp          | 2                           | 23
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment