CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_customer_order_date ON orders(customer_id, order_date);

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

SHOW INDEX FROM orders;

SELECT * FROM orders WHERE YEAR(created_at) = 2024;

SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

UPDATE orders SET status = 'archived' WHERE created_at < '2022-01-01';

  CREATE INDEX idx_orders_created_at ON orders(created_at);
  

  SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
  

  VACUUM ANALYZE orders;
  

Seq Scan on users  (cost=0.00..44231.00 rows=1000000 width=64)
  Filter: (status = 'active')

Index Scan using idx_users_status on users
  Index Cond: (status = 'active')

SELECT 
    o.OrderID,
    p.ProductName,
    p.Amount
FROM Orders o
CROSS APPLY (
    SELECT TOP 1 *
    FROM Products p
    WHERE p.OrderID = o.OrderID
    ORDER BY p.Amount DESC
) p;

SELECT 
    o.order_id,
    p.product_name,
    p.amount
FROM orders o,
LATERAL (
    SELECT *
    FROM products p
    WHERE p.order_id = o.order_id
    ORDER BY p.amount DESC
    LIMIT 1
) p;

-- формат dd.mm.yyyy
select try_convert(date, isnull(@fielddateBirth,'01.01.1980'), 104)

-- формат yyyy-mm-dd
select try_convert(date, isnull(@fielddateBirth,'1980-01-01'), 23)

-- формат dd.MM.yyyy
select try_convert(nvarchar(16), getdate(), 104)

-- формат yyyy-mm-dd
select convert(nvarchar, isnull(dateBirth,'1900-01-01'), 23)

select format(getdate(), 'dd.MM.yyyy HH:mm')

select getdate() -- получить текущую дату

select dateadd(day, 7, getdate()) -- добавить 7 дней к дате

select dateadd(month, -2, getdate()) -- отнять 2 месяца от даты

select datediff(minute, date1, date2) -- получить разницу в минутах между date2 и date1

SELECT DAY(GETDATE())       -- 28  получить день даты (аналогично month, year, week)

SELECT DATENAME(month, GETDATE())       -- July получить название месяца 

SELECT DATEPART(month, GETDATE())       -- 7 получить часть даты (например, месяц, год или день)

SELECT DATEFROMPARTS(2017, 7, 28)       -- 2017-07-28  строит дату из частей

SELECT ISDATE('2017-07-28')     -- 1 - проверка является ли строка датой 

-- начало и конец недели
SET DATEFIRST 4   /* or use any other weird value to test it */
DECLARE @d DATETIME = GETDATE()
SELECT
  @d ThatDate,
  cast( DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) as date) Monday,
  cast(DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) as date) Sunday


-- начало месяца
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0) AS StartOfMonth

-- номер недели в году 
set datefirst 1;
select datepart(week, '2017-02-01');

-- получение дня недели
select datename(dw,getdate()) --Thursday
select datepart(dw,getdate()) --2

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 5;

SELECT 
  calls, 
  total_time, 
  mean_time, 
  query 
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

ANALYZE my_big_table;

VACUUM ANALYZE my_big_table;

pg_basebackup -h replica.host -U repl_user -D /backup/pg -F tar -z -P

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

wait_event_type: Lock
wait_event: relation

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
  AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
  AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
  AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
  AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
  AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
  AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
  AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;