How to Debug Queries in ParadeDB

Galaxy Glossary

How can I debug slow SQL queries in ParadeDB?

Use EXPLAIN/ANALYZE, logging, and the auto_explain extension to trace and fix slow ParadeDB queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why is my ParadeDB query slow?

Start by timing the query with EXPLAIN ANALYZE. The plan reveals sequential scans, missing indexes, or mis-estimated row counts that often cause slowness.

How do I run EXPLAIN ANALYZE in ParadeDB?

Prefix the statement with EXPLAIN (ANALYZE, BUFFERS, VERBOSE). ParadeDB executes the query, then prints the actual run time, rows processed, and I/O statistics.

Can I log every slow query automatically?

Yes. Enable the auto_explain extension and set auto_explain.log_min_duration (ms). ParadeDB will log any query whose runtime exceeds that threshold, plus its execution plan.

How do I capture all SQL text that reaches the server?

Set log_statement = 'all' or 'mod' in postgresql.conf or the current session. Combine with log_duration = on to get timing for each statement.

Which plan nodes should I look for first?

Sequential scans on large tables, nested-loop joins with high row counts, and sorts spilling to disk (Sort Method: external merge) usually signal missing indexes or bad work_mem settings.

How can I test an index without creating it?

Use CREATE INDEX CONCURRENTLY to build the index online, run the query again, and then drop the index if it does not help. ParadeDB will choose the index automatically if beneficial.

What settings help during an investigation?

Temporarily raise work_mem, lower random_page_cost on SSDs, and enable track_io_timing. Always reset them after testing to avoid side effects in production.

Best practices for production debugging?

Run tests in a staging clone, keep auto_explain thresholds conservative, and store logged plans in a versioned repository so the team can compare past and present performance.

Why How to Debug Queries in ParadeDB is important

How to Debug Queries in ParadeDB Example Usage


-- Identify bottlenecks in a customer’s recent orders
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.order_date, SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p     ON p.id = oi.product_id
WHERE o.customer_id = 42
GROUP BY o.id, o.order_date
ORDER BY o.order_date DESC;

How to Debug Queries in ParadeDB Syntax


-- Show the execution plan without running the query
EXPLAIN SELECT *
        FROM Orders
        WHERE customer_id = 42;

-- Show actual timings and I/O usage
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT oi.quantity, p.name, p.price
FROM OrderItems oi
JOIN Products p ON p.id = oi.product_id
WHERE oi.order_id = 911;

-- Enable statement logging for the session
SET log_statement = 'all';
SET log_duration = on;

-- Auto-log plans for queries slower than 500 ms
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 500;

Common Mistakes

Only using EXPLAIN (without ANALYZE). This shows the planner’s estimate, not reality. Add ANALYZE to get actual runtime and row counts.
Leaving verbose logging on in production. Full statement logging floods disks and slows the server. Revert settings after the investigation.