вторник, 13 февраля 2018 г.

Иерархический запрос в postgres Аналог order siblings by


 WITH RECURSIVE temp1(hierarchy,id,parent_id,industry_tree_name,order_by,lvl) AS
     (
    select  ARRAY[it.industry_tree_id] AS hierarchy,
            it.industry_tree_id        as id,
            it.industry_tree_parent_id as parent_id,
            it.industry_tree_name,
            it.order_by,
            it.lvl
    from    industry_tree it
    where   it.industry_version_id = 11
    and     it.active_from <= current_timestamp
    and     it.active_to > current_timestamp
     union
    select  temp1.hierarchy || t2.industry_tree_id AS hierarchy,
            t2.industry_tree_id        as id,
            t2.industry_tree_parent_id as parent_id,
            t2.industry_tree_name,
            t2.order_by,
            temp1.lvl+1 as lvl
    from    industry_tree t2
    INNER JOIN temp1 on (temp1.id = T2.industry_tree_parent_id) 
       and     t2.active_from <= current_timestamp
       and     t2.active_to > current_timestamp
    )
     select *
       from temp1
ORDER  BY hierarchy;

Взято с stackoverflow.com

понедельник, 15 января 2018 г.

Вакуум, статистика

-- примерное количестов записей в таблице
 SELECT relname,reltuples AS approximate_row_count FROM pg_class where relowner=22268 and relkind='r' order by 2 desc

-- информация по вакууму и статистике
select 'vacuum analyze '||schemaname||'.'||relname||';' as tbl,last_vacuum,last_analyze,last_autovacuum,autovacuum_count,last_autoanalyze,
       (
        SELECT reltuples AS approximate_row_count
          FROM pg_class
         where relowner=22268 and relkind='r' and relname=att.relname
       ) as apx_row_nums
  from pg_stat_all_tables att
 where att.schemaname not in ('pg_catalog','pg_toast','prm_analytics','prm_ctp','public',
                              'prm_log','auth','prm_analytics_rep','information_schema','prm_root')
 --
 /*
 and
 autovacuum_count=0 and
 last_autovacuum is null and
 last_autoanalyze is null and
 last_vacuum is null
 --
 */
 order by att.autovacuum_count desc

 commit;

Статистика по запросам

 select pss.*
   from pg_stat_statements pss
  where pss.query not like 'FETCH ALL IN cursor%' and
        pss.query not like 'CLOSE cursor%' and
        pss.query not like '%privilege%' and
        pss.query not like 'SET datestyle%'
 order by pss.total_time desc

четверг, 28 декабря 2017 г.

Мониторинг использования, статистика

Взято с pgtune

select * from pg_stat_user_tables q order by q.seq_scan desc

select * from pg_statio_user_tables t order by t.heap_blks_read desc


Расширение CREATE EXTENSION pg_buffercache;

использование буферов объектами (таблицами, индексами, прочим):

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

relname             |buffers |
--------------------|--------|
sl_user_access_role |35119   |
sl_user_access      |18547   |
t_users             |17817   |
msg_content         |14132   |
sl_report_data      |13379   |


объекты (таблицы и индексы) в кэше:


 SELECT c.relname, count(*) AS buffers,usagecount
 FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

relname                          |buffers |usagecount |
---------------------------------|--------|-----------|
sl_user_access_role              |35119   |5          |
sl_user_access                   |18546   |5          |
t_users                          |17817   |5          |
msg_content                      |14147   |5          |
sl_report_data                   |13396   |5          |
pk_sl_f_cons_rep                 |10942   |5          |
pk_sl_f_form_rep                 |10520   |5          |


Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):


SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20;

relname                    |buffered |buffers_percent |percent_of_relation |
---------------------------|---------|----------------|--------------------|
sl_user_access_role        |274 MB   |6.7             |100.0               |
sl_user_access             |145 MB   |3.5             |100.0               |
t_users                    |139 MB   |3.4             |100.0               |
msg_content                |111 MB   |2.7             |100.0               |
sl_report_data             |105 MB   |2.6             |99.9                |
pk_sl_f_cons_rep           |85 MB    |2.1             |98.3                |
pk_sl_f_form_rep           |82 MB    |2.0             |98.5                |
mv_budgets_last_sum        |82 MB    |2.0             |100.0               |
sl_f_form_rep              |40 MB    |1.0             |99.9                |
sl_report_requisites       |36 MB    |0.9             |20.2                |

sl_f_cons_rep              |39 MB    |0.9             |99.9                |

Далее берем топ таблицу и смотрим в каких процедурах она используется и как, все ли индексы на месте.

select proname,prosrc from pg_proc where proname= your_function_name; 





четверг, 30 ноября 2017 г.

Интервал выполнения и sleep

do $$
DECLARE
t NUMERIC;
BEGIN
select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

perform pg_sleep(2);

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;


select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

end $$

----------------- 

00000: t=1512065827.6849
00000: t=1512065829.6977
00000: t=1512065829.69793

среда, 11 октября 2017 г.

BAT file for backup postgres schema

@echo off
set PGPASSWORD=prm_salary
set PGUSER=prm_salary
set PGDATABASE=db_ris_mkrpk
set PGBIN="C:\Program Files\PostgreSQL\9.6\bin\"
set t=%date%_%time%
set d=%t:~10,4%%t:~7,2%%t:~4,2%_%t:~15,2%%t:~18,2%%t:~21,2%
set BACKUP_FILE="C:\dumps\prm_salary_%d%.dmp"
set FILELOG="C:\dumps\prm_salary_%d%.log"
(
 echo Backup start %date%  %time% file %BACKUP_FILE%
 %PGBIN%"pg_dump" --schema=prm_salary --format=c --file=%BACKUP_FILE%
 echo End of backup %date%  %time%
)>> %FILELOG% 2>&1
forfiles /p "C:\dumps" /s /m *.log /D -5 /C "cmd /c del @path"
forfiles /p "C:\dumps" /s /m *.dmp /D -5 /C "cmd /c del @path"

среда, 20 сентября 2017 г.

Блокировки


-- запросы онлайн
SELECT * FROM pg_stat_activity order by client_addr;

-- блокировки по пидам
SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid()

--3
SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='cabinet' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();



SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0

SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='cabinet';