Originally published on the Hubfly space blog. By Octave, drafted with AI, tested on Hubfly space.

TL;DR

  • What you get: a repeatable way to find the queries that cost your database the most time, prove why they're slow, and fix them, with before-and-after numbers.
  • Who it's for: developers running Postgres for a real app who don't have a DBA.
  • Time needed: about 20 minutes, plus a few minutes for the database to start.
  • Cost: a Nano managed Postgres on Hubfly space is $2.02 a month (about $0.003 an hour). Delete the instance when you're done and following along costs well under a cent.
  • Checked on: PostgreSQL 18.6 on a Hubfly space Nano instance, October 2026.

Your app was fast in the demo. Now the orders page takes a second to load and nobody knows why. You could guess, add a few indexes and hope. Or you could ask Postgres which queries actually eat its time, fix the worst one, and measure the result.

In this guide you'll do exactly that. Slow queries cost you users, and on a small database they also cost you money, because the usual fix is to buy a bigger server. A missing index often costs more than an undersized server.

Try it on Hubfly space: the managed Postgres used here starts at $2.02 a month and every query-tuning tool in this guide is included at no extra cost.

How it works in one picture 

your app ──► Postgres ──► pg_stat_statements (counts every query shape) │ ▼ 1. rank by total time ──► 2. EXPLAIN ANALYZE the winner │ ▼ 4. reset + measure again ◄── 3. add the right index

pg_stat_statements is a Postgres extension that groups queries by their shape (WHERE customer_id = $1, not = 4242) and records how often each one ran and how long it took. That's the data you need, because the query that hurts you is rarely the slowest one. It's the one that's a bit slow and runs all the time.

Step 1: Create a Postgres database

If you already have Postgres with pg_stat_statements, skip to Step 3.

  1. In the Hubfly space dashboard, click Create Project, name it (for example shop), choose Managed service and click Create Project.
  2. Click Create a database, keep PostgreSQL 18 and the Nano size (0.25 vCPU, 512 MiB, 10 GiB storage).
  3. Name the instance shop-db and click Create database.

The Create database form with the Nano price breakdown

The price panel on the Create database form: $0.00277 an hour while running, $2.02 a month.

The instance usually reaches Running within a few minutes. Hubfly space managed Postgres preloads pg_stat_statements (SHOW shared_preload_libraries; returns it), so you don't have to restart anything to start collecting data. On a self-managed server you'd add it to postgresql.conf and restart first.

Step 2: Load some realistic data

You need enough rows for a slow query to be slow. Open SQL editor and switch off Read-only. The editor warns that writes change your data immediately and that there are no backups (automatic backups are still coming soon), so do this on a test instance, never on production. Paste this and click Run:

CREATE TABLE customers ( id bigserial PRIMARY KEY, email text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); INSERT INTO customers (email) SELECT 'user' || g || '@example.com' FROM generate_series(1, 50000) g; CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(id), status text NOT NULL, total_cents int NOT NULL, created_at timestamptz NOT NULL ); INSERT INTO orders (customer_id, status, total_cents, created_at) SELECT 1 + (random() * 49999)::int, (ARRAY['pending','paid','shipped','refunded'])[1 + (random() * 3)::int], (random() * 20000)::int, now() - random() * interval '365 days' FROM generate_series(1, 300000); ANALYZE customers; ANALYZE orders;

That's 50,000 customers and 300,000 orders. On a Nano instance the whole script takes about 12 seconds.

Note one thing that's easy to miss: Postgres creates an index for a PRIMARY KEY, but not for a foreign key. orders.customer_id has no index. That's the most common cause of slow pages in real apps.

Step 3: Run your app's queries

In real life your app does this part. Here, simulate it by running the three queries an order-history page and a dashboard would run. Paste the whole block into the editor and click Run six times:

-- "My orders" page, for five different customers SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20; SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 17 ORDER BY created_at DESC LIMIT 20; SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 30001 ORDER BY created_at DESC LIMIT 20; SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 999 ORDER BY created_at DESC LIMIT 20; SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 25000 ORDER BY created_at DESC LIMIT 20; -- Customer lookup SELECT email FROM customers WHERE id = 4242; SELECT email FROM customers WHERE id = 17; -- Admin dashboard: last 30 days by status SELECT status, count(*), sum(total_cents) FROM orders WHERE created_at > now() - interval '30 days' GROUP BY status;

If your database already serves real traffic, skip this step. Real traffic is better data.

Step 4: Find the query that costs the most time

Open Query insights. Before you read it, click Reset statistics and run your workload again, so setup statements like the big INSERT don't drown out the real queries. Resetting only clears the counters; your data isn't touched.

Query insights before the fix: one query takes 60% of all database time

Statement statistics before the fix: the orders lookup is 60% of all query time.

Read the Where the time goes chart from the top:

  • orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2: 30 calls, mean 45 ms, total 1.36 s, 60%
  • orders WHERE created_at > now() - interval $1 GROUP BY status: 5 calls, mean 167 ms, total 836 ms, 37%
  • customers WHERE id = $1: 12 calls, mean 6.32 ms, total 76 ms, 3.3%

Your call counts and timings may differ slightly.

Sort by total time, not by mean. The dashboard query has the worst mean (167 ms), but the orders lookup runs far more often and costs more overall. Fix what costs the most first.

Prefer SQL? Query the view yourself

The dashboard reads pg_stat_statements for you. To query the view from psql or your own tools, install it in your database from the Extensions tab (find pg_stat_statements and click Install). Running CREATE EXTENSION pg_stat_statements yourself fails with permission denied to create extension because the admin user isn't a superuser; the Extensions tab does it for you. Then:

SELECT round(total_exec_time::numeric, 1) AS total_ms, calls, round(mean_exec_time::numeric, 2) AS mean_ms, rows, left(query, 70) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

The admin user can read every user's statistics, but it can't call pg_stat_statements_reset(). Use Reset statistics in Query insights for that.

Step 5: Ask Postgres why it's slow

Copy the top query, put real values back in, and prefix it with EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE showing a sequential scan that removes 299,992 rows

The plan before the fix: a sequential scan that reads 2,500 pages to return 8 rows.

Three lines tell the whole story:

  • Seq Scan on orders: Postgres reads the entire table.
  • Rows Removed by Filter: 299992: it throws away almost everything it read.
  • Buffers: shared hit=2503: it touched 2,503 pages (about 20 MB) to return 8 rows.

That took 15.8 ms here with the table already in memory. On a busy server, with a bigger table or a cold cache, the same plan takes far longer, and it gets worse every day as orders grows.

Careful: EXPLAIN ANALYZE really runs the statement. For an UPDATE or DELETE, wrap it in BEGIN; … ROLLBACK; or use plain EXPLAIN, which only plans.

Step 6: Add the right index, without locking the table

The query filters on customer_id and sorts by created_at, so one index on both columns, in that order, serves both:

CREATE INDEX CONCURRENTLY orders_customer_id_created_at_idx ON orders (customer_id, created_at DESC);

CONCURRENTLY builds the index while your app keeps writing to the table. A plain CREATE INDEX blocks inserts and updates until it finishes, which on a large production table can mean minutes of failed checkouts. It took 1.02 s on 300,000 rows. Run it as its own statement: CONCURRENTLY can't run inside a transaction.

While you're here, give the dashboard query the obvious index too, one on created_at (Step 7 shows why it isn't enough on its own):

CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at);

Now run the same EXPLAIN again:

EXPLAIN ANALYZE after the index: an index scan in 0.161 ms

The plan after the fix: an index scan, 14 pages, 0.161 ms.

Index Scan using orders_customer_id_created_at_idx, 14 pages instead of 2,503, and 0.161 ms instead of 15.8 ms. The separate sort step is gone too, because the index already stores rows in created_at DESC order.

Step 7: Measure again, then fix the next one

Click Reset statistics, run the same workload, and open Query insights again.

Query insights after the fix: total time drops from 2.27 s to 409 ms

After the two indexes: total query time for the workload drops from 2.27 s to 409 ms.

The composite index took the orders lookup from a 45 ms mean to 0.10 ms over the same 30 calls. With both indexes in place, total time for the workload fell from 2.27 s to 409 ms. And notice what happened: the dashboard query is now 99% of the remaining time, still averaging 67 ms. That's normal. Each fix promotes the next problem to the top. Repeat Steps 5 and 6 on it.

When a normal index isn't enough

The plain created_at index from Step 6 didn't do much for the dashboard query. EXPLAIN shows Postgres using it (Bitmap Index Scan on orders_created_at_idx) but still visiting Heap Blocks: exact=2500, every page of the table, because 30 days of orders are scattered across all of it. It still took 95 ms.

The fix is a covering index: store the columns the query reads inside the index, so Postgres never visits the table.

CREATE INDEX CONCURRENTLY orders_created_at_covering_idx ON orders (created_at) INCLUDE (status, total_cents); VACUUM (ANALYZE) orders; DROP INDEX CONCURRENTLY orders_created_at_idx; -- the plain index is now unused

Run each statement on its own. Because DROP INDEX removes an object, the editor asks "Run destructive statements?"; check the name and click Run anyway. The plan changes to Index Only Scan … Heap Fetches: 0, 125 pages instead of 2,570, and 6.7 ms instead of 95 ms. The VACUUM matters: index-only scans need Postgres's visibility map to be up to date, which autovacuum also does on its own schedule. Dropping the unused index matters too, because every index slows down writes a little.

Step 8: Catch the next slow query before users do

Query insights shows totals. To see each slow query as it happens, log it.

  1. Open Configuration, find log_min_duration_statement and set it to 250 (milliseconds; use a lower value like 50 while tuning).
  2. Click Review changes. The review shows the diff and says "These settings apply live without a restart." Add a note and click Apply live.
  3. Open Logs and search for duration:.

Logs filtered by duration showing slow statements with their time

Every statement over the threshold appears with its duration and text.

Out of the box the instance logs statements slower than 1 second. Logs can contain query text and data values, so don't share them carelessly.

Then open Alerts and make sure you hear about trouble without watching the dashboard. Storage and connection thresholds are set to 85% and 90% (you can choose 50–99%). Instance down and Email me are off by default: turn both on and click Save changes.

Alerts page with Instance down and Email me turned on

Alerts with Instance down and email turned on.

What can go wrong

1. "relation pg_stat_statements does not exist"

The library is loaded, but the view only exists in a database after CREATE EXTENSION. Install it from the Extensions tab for each database you query from. Query insights works without this step.

2. "permission denied to create extension", or a permission error when you reset

The admin user isn't a superuser, so it can't create untrusted extensions or call pg_stat_statements_reset(). Use the Extensions tab to install and Reset statistics in Query insights to reset.

3. "CREATE INDEX CONCURRENTLY cannot run inside a transaction block"

Some migration tools wrap every file in a transaction. Run the statement on its own, or mark that migration as non-transactional (for example disable_ddl_transaction! in Rails, or atomic = False in a Django migration). If a concurrent build fails, it leaves an INVALID index behind; drop it and try again.

4. You added an index and nothing changed

Run EXPLAIN again. If the plan still says Seq Scan, the column order may be wrong (put the = column first, the sort or range column second), the table may be too small for an index to be worth it, or the query wraps the column in a function (lower(email)) so you need an expression index. If it says Bitmap Heap Scan with every block visited, try a covering index as in Step 7.

Takeaway

Don't guess which query is slow. Rank by total time, EXPLAIN (ANALYZE, BUFFERS) the winner, fix it with an index built CONCURRENTLY, then reset and measure. Here, the composite index took the busiest query from 45 ms to 0.10 ms, the two indexes together cut total time for the workload by 82% (2.27 s down to 409 ms), and a covering index then took the next query from 95 ms to 6.7 ms. The cost of the database didn't change: the Nano instance is still $2.02 a month.

Finished experimenting? Delete the test instance from its Settings tab (Delete instance, then type its name to confirm). Storage is billed while an instance exists, even when it's stopped.

Next, read the docs on monitoring and operating a managed database for sessions, metrics and safe config changes.

Deploy this

Create a managed Postgres on Hubfly space, load your schema, and open Query insights. Query insights, logs, alerts and the config editor come with every instance at no extra cost.

Go further on Hubfly space

  • Connect your app safely. Use the Apps tab to bind the database to a Hubfly space Cell app; it creates a dedicated user and a DATABASE_URL secret for you. See Databases.
  • Lock down access. Restrict connections to an allowlist of your IPs under Network. See Network access.
  • Give reporting tools a read-only user. Create one under Users & roles so a dashboard can never change data. See Users, databases and tokens.