Где посмотреть запрос в PostgreSQL?

Где посмотреть запрос в PostgreSQL

Этот вопрос касается отладки SQL запросов в PostgreSQL. Есть несколько способов увидеть и проанализировать запросы, которые выполняются в базе данных.

1. Логирование запросов на уровне PostgreSQL

Это самый надёжный способ увидеть ВСЕ запросы, выполняемые в БД.

Конфигурация postgresql.conf

# Найти файл конфигурации
sudo -u postgres psql -c "SHOW config_file;"
# /etc/postgresql/14/main/postgresql.conf

# Отредактировать файл
sudo nano /etc/postgresql/14/main/postgresql.conf

# Добавить или изменить эти параметры:
log_statement = 'all'              # Логировать ВСЕ запросы
log_duration = on                  # Показывать время выполнения
log_min_duration_statement = 0     # Логировать даже быстрые запросы (0 мс)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on

# Перезагрузить PostgreSQL
sudo systemctl restart postgresql

Просмотр логов

# Логи обычно находятся в:
tail -f /var/log/postgresql/postgresql-14-main.log

# Или на macOS с Homebrew:
tail -f /usr/local/var/log/postgres.log

# Вывод будет примерно:
# 2024-03-22 12:34:56.789 UTC [1234]: [1-1] user=myuser,db=mydb,app=psql,client=127.0.0.1
# LOG: statement: SELECT * FROM users WHERE id = 1;
# 2024-03-22 12:34:56.795 UTC [1234]: [2-1] user=myuser,db=mydb,app=psql,client=127.0.0.1
# LOG: duration: 6.123 ms

2. Встроенный pg_stat_statements расширение

Это расширение отслеживает все запросы в памяти.

-- 1. Включить расширение (требуется суперпользователь)
CREATE EXTENSION pg_stat_statements;

-- 2. Просмотреть все запросы
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Вывод:
-- query                           | calls | total_time | mean_time
-- SELECT * FROM users WHERE id = ?| 100   | 156.234   | 1.562
-- SELECT * FROM orders           | 50    | 234.567   | 4.691

-- 3. Просмотреть запросы конкретного пользователя
SELECT userid, query, calls, total_time
FROM pg_stat_statements
WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = 'myuser')
ORDER BY total_time DESC;

-- 4. Очистить статистику
SELECT pg_stat_statements_reset();

3. Просмотр активных запросов в реальном времени

-- Посмотреть все активные соединения и их запросы
SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    state_change
FROM pg_stat_activity
WHERE state != 'idle';

-- Вывод:
-- pid  | usename | application_name | state  | query             | query_start
-- 1234 | myuser  | psql             | active | SELECT * FROM ... | 2024-03-22 12:34:56
-- 5678 | admin   | DBeaver          | active | UPDATE users ...  | 2024-03-22 12:35:01

-- Отслеживать медленные запросы (более 5 секунд)
SELECT 
    pid,
    usename,
    query,
    EXTRACT(EPOCH FROM (now() - query_start)) as duration_seconds
FROM pg_stat_activity
WHERE state = 'active'
  AND EXTRACT(EPOCH FROM (now() - query_start)) > 5;

-- Убить зависший запрос
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 1234;  -- PID зависшего процесса

4. EXPLAIN для анализа плана запроса

-- Показать план выполнения запроса
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Вывод:
-- QUERY PLAN
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1)
--   Index Cond: (id = 1)

-- Подробный анализ с реальным временем
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- Вывод показывает estimated vs actual rows, время выполнения
-- QUERY PLAN
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1)
--   (actual time=0.023..0.024 rows=1 loops=1)
-- Index Cond: (id = 1)
-- Planning Time: 0.042 ms
-- Execution Time: 0.044 ms

-- Формат JSON (для анализа программно)
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

5. Логирование на уровне приложения Java

Использование логирования JDBC

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.postgresql.Driver;

public class JdbcLogging {
    private static final Logger logger = LoggerFactory.getLogger(JdbcLogging.class);
    
    public static void main(String[] args) throws SQLException {
        // Включить логирование PostgreSQL драйвера
        System.setProperty("org.postgresql.driver.logUnclosedConnections", "true");
        
        String url = "jdbc:postgresql://localhost:5432/mydb";
        try (Connection conn = DriverManager.getConnection(url, "user", "password")) {
            Statement stmt = conn.createStatement();
            
            String query = "SELECT * FROM users WHERE id = 1";
            logger.info("Executing query: {}", query);
            
            long startTime = System.currentTimeMillis();
            ResultSet rs = stmt.executeQuery(query);
            long duration = System.currentTimeMillis() - startTime;
            
            logger.info("Query executed in {} ms", duration);
            
            while (rs.next()) {
                logger.debug("Row: id={}, name={}", rs.getInt(1), rs.getString(2));
            }
        }
    }
}

Использование Hibernate/JPA логирования

# application.properties (Spring Boot)
# Логировать SQL запросы
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# Логировать параметры запроса
spring.jpa.properties.hibernate.use_sql_comments=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

<!-- logback.xml для Spring Boot -->
<logger name="org.hibernate.SQL" level="DEBUG" />
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE" />

6. Утилита pgAdmin для визуального просмотра

1. Откройте pgAdmin web интерфейс
2. Перейдите к Tools → Query Tool
3. Выполните запрос
4. Внизу окна отобразится актуальный SQL
5. Нажмите кнопку "Explain" для анализа плана

7. Встроенная команда psql \explain

psql -U myuser -d mydb

# В интерактивном режиме psql
mydb=# EXPLAIN SELECT * FROM users WHERE id = 1;
mydb=# EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

# Включить расширенный режим вывода
mydb=# \x on
mydb=# EXPLAIN (ANALYZE true, BUFFERS true) SELECT * FROM users;

8. Практический пример: отладка медленного запроса

public class SlowQueryDebug {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        
        try (Connection conn = DriverManager.getConnection(url, "user", "password")) {
            String query = """  
                SELECT u.*, COUNT(o.id) as order_count
                FROM users u
                LEFT JOIN orders o ON u.id = o.user_id
                WHERE u.created_at > '2024-01-01'
                GROUP BY u.id
                ORDER BY u.created_at DESC
            """;
            
            // 1. Логируем запрос
            System.out.println("Query: " + query);
            
            // 2. Анализируем план
            try (Statement stmt = conn.createStatement()) {
                String explainQuery = "EXPLAIN ANALYZE " + query;
                ResultSet rs = stmt.executeQuery(explainQuery);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
            
            // 3. Выполняем и измеряем время
            long startTime = System.currentTimeMillis();
            try (Statement stmt = conn.createStatement()) {
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                    // Обработка результатов
                }
            }
            long duration = System.currentTimeMillis() - startTime;
            
            System.out.println("Execution time: " + duration + " ms");
        }
    }
}

9. Проверка индексов и статистики

-- Посмотреть используемые индексы
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE tablename = 'users';

-- Проверить статистику по таблице
SELECT 
    schemaname,
    tablename,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- Обновить статистику таблицы
ANALYZE users;

-- Пересчитать статистику для всех таблиц
ANALYZE;

Рекомендуемый набор инструментов

1. pg_stat_statements — для мониторинга в production
2. pgAdmin — для визуального анализа
3. Logback/SLF4J — для логирования в приложении
4. EXPLAIN ANALYZE — для отладки конкретных запросов
5. pg_stat_activity — для просмотра активных запросов
6. DBeaver — профессиональный SQL client с расширенной аналитикой

Итог

Для просмотра запросов в PostgreSQL:

• В production: используйте pg_stat_statements
• Для отладки: используйте EXPLAIN ANALYZE
• В реальном времени: смотрите pg_stat_activity
• Для логирования: включите log_statement = 'all' в postgresql.conf
• Из Java приложения: добавьте логирование JDBC и Hibernate

Это комплексный подход позволяет найти и устранить проблемы с производительностью запросов.