DK Damir Khaibulin
🇷🇺 RU Russian 🇬🇧 EN English 🇩🇪 DE German 🇫🇷 FR French 🇮🇹 IT Italian 🇪🇸 ES Spanish
Let's talk
← Back to articles
Performance & Databases

Query Optimization: EXPLAIN and EXPLAIN ANALYZE in PostgreSQL + Other Analytical Tools

A practical guide to PostgreSQL query analysis: EXPLAIN and EXPLAIN ANALYZE, plus pg_stat_statements, auto_explain, pgBadger, Laravel Telescope, and Percona PMM with real examples.

Let's talk ↗ Read Article
Available languages
EN RU DE FR IT ES
✦
Featured article ↗

Query Optimization: EXPLAIN and EXPLAIN ANALYZE in PostgreSQL + Other Analytical Tools

📊 What are EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN is a command in PostgreSQL (and other SQL databases) that shows the execution plan of a query without actually running it. It answers the question: "How does the DBMS plan to execute this query?"

EXPLAIN ANALYZE is a more powerful version that actually executes the query and shows real execution metrics: time, row count, indexes used, etc.

🎯 Why Do We Need These?

In the era of big data and high-load applications, understanding database performance is a critical skill. These tools help:

  • Identify bottlenecks in performance
  • Optimize indexes and database structure
  • Understand the behavior of the query optimizer
  • Reduce load on the database server

📈 Example Usage in Laravel with PostgreSQL

// Simple EXPLAIN $explain = DB::select('EXPLAIN SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explain); // EXPLAIN ANALYZE $explainAnalyze = DB::select('EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explainAnalyze); // Using macros for convenience DB::macro('explain', function ($query, $bindings = []) { return DB::select('EXPLAIN ' . $query, $bindings); }); DB::macro('explainAnalyze', function ($query, $bindings = []) { return DB::select('EXPLAIN ANALYZE ' . $query, $bindings); }); // Example of macro usage $users = DB::explain('SELECT * FROM users WHERE active = true');

🔍 Analyzing EXPLAIN Results

Typical output includes:

Seq Scan on users (cost=0.00..15.00 rows=500 width=44) Filter: (email = 'user@example.com'::text)
  • Seq Scan — sequential scan (can be slow)
  • Index Scan — index scan (faster)
  • cost — estimated cost (lower is better)
  • rows — expected number of rows

🛠 Other Analytical Tools for PostgreSQL

1. pg_stat_statements — Query Execution Monitoring

-- Enable the extension CREATE EXTENSION pg_stat_statements; -- Top most expensive queries SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2. auto_explain — Automatic Slow Query Analysis

-- Enable in postgresql.conf shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '100ms' -- log queries >100ms

3. pgBadger — PostgreSQL Log Analyzer

# Installation and usage pgbadger /var/log/postgresql/postgresql-*.log -o report.html

4. Laravel Telescope — Built-in Laravel Diagnostics

// Enable query monitoring in config/telescope.php 'watchers' => [ QueryWatcher::class => [ 'enabled' => env('TELESCOPE_QUERY_WATCHER', true), 'slow' => 100, // slow queries >100ms ], ]

5. Percona Monitoring and Management (PMM) — Comprehensive Database Monitoring

Open-source platform for monitoring and managing MySQL, MongoDB, and PostgreSQL performance.

6. EXPLAIN (FORMAT JSON) — Detailed Query Analysis

-- Get detailed execution plan in JSON format EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM users;

🚀 Practical Optimization Examples in Laravel

Optimizing N+1 Problem

// BAD: N+1 queries $users = User::all(); foreach ($users as $user) { echo $user->posts->count(); // Separate query for each user } // GOOD: Eager loading $users = User::with('posts')->get(); foreach ($users as $user) { echo $user->posts->count(); // All data already loaded }

Analyzing Complex Queries with Index Usage

// Create an index Schema::table('orders', function (Blueprint $table) { $table->index(['user_id', 'created_at']); }); // Analyze the query $analysis = DB::explainAnalyze(" SELECT users.name, COUNT(orders.id) as order_count FROM users JOIN orders ON users.id = orders.user_id WHERE orders.created_at > NOW() - INTERVAL '30 days' GROUP BY users.id HAVING COUNT(orders.id) > 5 ");

📊 Tool Comparison Guide

Let's explore the key differences between database performance analysis tools:

EXPLAIN shows the theoretical execution plan without running the query. It's perfect for preliminary query analysis when you want to understand how PostgreSQL plans to execute your SQL statement. Use it during query development to catch potential performance issues early.

EXPLAIN ANALYZE goes further by actually executing the query and providing real performance metrics. This tool reveals actual execution times, row counts, and resource usage. It's essential for identifying real bottlenecks in production-like environments but should be used cautiously on production systems due to query execution.

pg_stat_statements offers comprehensive statistics about all queries executed in your database. It tracks execution frequency, total time, and average execution time across all queries. This is invaluable for identifying the most expensive queries in your application over time.

auto_explain automatically logs slow queries based on configurable thresholds. Once configured, it works silently in the background, capturing execution plans for queries exceeding your defined duration threshold. Perfect for ongoing performance monitoring without manual intervention.

Laravel Telescope provides application-level query monitoring within the Laravel ecosystem. It shows queries in the context of your application, including Eloquent relationships, HTTP requests, and job processing. Ideal for development and staging environments.

pgBadger analyzes PostgreSQL log files to generate detailed HTML reports about database performance. It helps identify patterns, slow queries, and connection issues over extended periods.

Percona Monitoring and Management offers enterprise-grade monitoring with visualization dashboards, alerting systems, and query analytics across multiple database technologies.

💡 Practical Tips

  1. Start with EXPLAIN for quick analysis
  2. Use EXPLAIN ANALYZE for precise data
  3. Create indexes based on analysis results
  4. Monitor regularly — performance changes with data growth
  5. Test with realistic data volumes
  6. Combine tools for comprehensive analysis
  7. Set up alerts for performance degradation

🔗 Conclusion

Understanding query analysis tools is not a luxury but a necessity for modern developers. In the Laravel ecosystem, we have powerful tools both at the database level (PostgreSQL) and framework level (Telescope).

Key takeaways:

  • Always analyze queries before deployment
  • Indexes solve 80% of performance problems
  • Regular monitoring prevents performance degradation
  • Combine multiple tools for complete visibility
  • Test performance under realistic conditions
Technologies & topics

Article tags

No articles match these filters.

Have a project or an idea to discuss?

Let's talk ↗