How to Optimize Query Performance in Retool

Open the Retool Debug Panel (bug icon, bottom-left). Switch to the Queries tab. Run your app and look at the execution times displayed next to each query in the waterfall. Queries are listed with their duration in milliseconds. Identify queries over 300ms — these are your optimization targets. Click on a query to see its request/response details including the full result payload size.

Create a temporary SQL query in Retool named 'debugSlowQuery'. Prepend EXPLAIN ANALYZE to the slow query's SQL. Run it to see the query execution plan. Look for: 'Seq Scan' on large tables (indicates missing index), high row estimates vs actual rows (stale statistics), expensive Sort operations (missing sort index), and high cost operations in nested loops. The plan shows the cost of each operation in relative units.

1-- Debug query: Run EXPLAIN ANALYZE on a slow query
2-- Replace the inner SELECT with your slow query
3EXPLAIN ANALYZE
4SELECT
5  o.id, o.customer_id, o.status, o.created_at
6FROM orders o
7JOIN customers c ON o.customer_id = c.id
8WHERE o.status = 'pending'
9  AND o.created_at > NOW() - INTERVAL '30 days'
10ORDER BY o.created_at DESC;
11
12-- Look for:
13-- 'Seq Scan on orders' on large tables → needs index
14-- High 'actual rows' with 'rows=1' estimate → run ANALYZE orders;
15-- 'Sort (cost=xxxxx)' → add index for sort column

After identifying sequential scans in EXPLAIN ANALYZE, create indexes on the relevant columns. Run CREATE INDEX statements in a Retool SQL query against your database. Focus on: columns in WHERE clauses with high selectivity (status, created_at, customer_id), columns used in JOIN ON clauses, and columns in ORDER BY clauses for large result sets. After creating indexes, re-run EXPLAIN ANALYZE to confirm 'Seq Scan' changed to 'Index Scan'.

1-- Create indexes for common query patterns
2-- Run these in a one-time SQL query in Retool
3
4-- Index for status + date range queries
5CREATE INDEX CONCURRENTLY idx_orders_status_created
6  ON orders(status, created_at DESC)
7  WHERE status IN ('pending', 'processing');
8
9-- Index for customer JOIN
10CREATE INDEX CONCURRENTLY idx_orders_customer_id
11  ON orders(customer_id);
12
13-- Composite index for a common filter combination
14CREATE INDEX CONCURRENTLY idx_orders_assigned_status
15  ON orders(assigned_to, status)
16  WHERE status != 'completed';
17
18-- Update table statistics after creating indexes
19ANALYZE orders;

SELECT * fetches all columns from the database, serializes them all to JSON, and sends them over the network. For a table with 40 columns, this can generate 10x more data than displaying 4 columns requires. The fix is simple: replace SELECT * with an explicit list of only the columns your components actually display. Check the Table Inspector to see which columns are configured — only select those.

1-- ❌ Before: SELECT * fetches all 42 columns
2SELECT * FROM orders o
3JOIN customers c ON o.customer_id = c.id
4JOIN products p ON o.product_id = p.id;
5
6-- ✅ After: SELECT only displayed columns
7SELECT
8  o.id,
9  o.order_number,
10  c.name AS customer_name,
11  c.email AS customer_email,
12  o.status,
13  o.total_amount,
14  o.created_at
15FROM orders o
16JOIN customers c ON o.customer_id = c.id
17WHERE o.status = {{ statusFilter.value || 'active' }}
18ORDER BY o.created_at DESC
19LIMIT 200;

A common anti-pattern: fetch all rows from the database, then filter them client-side in a Retool Transformer. This is inefficient because the database fetches more rows than needed, the full unfiltered dataset is serialized and sent over the network, and the browser parses the full dataset before the transformer runs. Always push filter conditions into SQL WHERE clauses. Use {{ }} parameters in the WHERE clause so they update dynamically.

1-- ❌ Anti-pattern: Transformer filtering client-side
2-- Query fetches ALL orders (could be 50,000 rows)
3SELECT * FROM orders;
4
5-- Transformer: filters to just the selected status
6return data.filter(row => row.status === statusSelect.value);
7
8-- ✅ Better: WHERE clause in SQL (server-side filtering)
9-- Query only fetches matching rows
10SELECT id, order_number, status, total_amount, created_at
11FROM orders
12WHERE status = {{ statusSelect.value }}
13ORDER BY created_at DESC
14LIMIT 500;

Very slow queries (>30 seconds) can freeze the Retool UI. Configure a query timeout in the Advanced tab to set a maximum execution time. If the query exceeds this timeout, it returns an error that you can handle gracefully instead of leaving the user on a frozen screen. Set the timeout in seconds — for dashboard queries, 15-30 seconds is a reasonable limit. Add an 'On failure' handler that shows a notification suggesting the user narrow their filter.

1// Query Advanced tab settings:
2// Query timeout: 15 (seconds)
3
4// On failure event handler JS Query:
5if (queryName.error?.message?.includes('timeout')) {
6  utils.showNotification({
7    title: 'Query timed out',
8    description: 'Too much data for this date range. Try narrowing the filter.',
9    notificationType: 'warning',
10    duration: 8,
11  });
12}