When Alcuin feels slow

Most slowness has one of three sources: the query itself, the size of what comes back, or the distance to the server. Find which one first. Each section below starts from what you notice.

A query takes a long time to start returning rows

While a statement runs, the result bar shows Running for and the time. Until the first rows arrive, the time is the server’s: Alcuin is waiting for Postgres.

1. Explain the query to see what the server does with it: click Explain, or press ⌘⌥E on macOS or Ctrl+Alt+E on Windows and Linux. Find out why something is slow shows how to read the plan and act on it.
2. If the query is usually fast, it may be waiting for a lock another session holds. Open Activity from the command palette with Show activity. Find what is stuck explains the blocking tree.
3. If the whole server is slow, check Health and Top statements, from Home or the command palette, for what the database spends its time on.

To stop a statement, click Cancel or press ⌘. on macOS or Ctrl+. on Windows and Linux. To have the server stop long statements for you, set Stop statements after, in Settings under Safety, or Stop queries after on a connection. On production it is 30 seconds, and can only be made shorter. See Work on production without breaking it.

A result stops before the end

Alcuin reads a result up to its first 100,000 rows, 32 MB or 10 seconds (5 on production), then lets go of the database. When it stops early, the result bar says Showing the first 100,000 rows. The rest weren't read. This keeps a large result from holding a session, or memory, open.

• To see every row, click Read the rest. Write and iterate on a query says when it carries on and when it runs the query again.
• To know how many rows there are without scrolling, click Count beside the row count. It reads the rest of the open result, so the query does not run again.
• To get what you need sooner, add a WHERE or a LIMIT. A result you can read is usually a result you can narrow.

On production, Ask before large results asks first when the planner expects a query with no LIMIT to return more than 100,000 rows. You can set the same question for other environments in Settings, under Safety.

The grid is slow to scroll or fill

The grid fetches rows a page at a time. Set the page size in Settings, under Behavior, with Rows per page: 100, 200 (the default), 500 or 1000.

• On a slow or distant connection, a larger page means fewer round trips while you scroll.
• On a table with very wide rows, such as large jsonb or text values, a smaller page brings the first rows sooner.

A table’s Data tab sends its own SELECT for each page, with your filters and sort. A filter or a sort on a column with no index makes the server read the whole table for every page. Click Explain this page on the Data tab’s toolbar to see the plan of the exact statement the page ran. A contains filter usually reads every row whatever the indexes.

Row counts say ≈ or stop at 1,000

Counting every row of a large table is slow, so Alcuin shows estimates until it has the real number:

• A table’s Data tab shows the server’s estimate, such as ≈12,400,000 rows, until the grid reaches the end. With a filter on, it counts the rows it has read so far, such as 200+ rows. When a filter matches nothing, it says how many rows the table has in total, from the same estimate.
• The row panel counts the rows that point at a row up to 1,000, and says 1,000+ past that.
• A query result says First 200 rows · more until you scroll or click Count.

An estimate far from the truth means the table’s statistics are out of date. Health lists tables that need ANALYZE, and the same misestimate slows the planner too. See Find out why something is slow.

The Explorer is slow to show or update

Alcuin keeps the list of schemas, tables and other objects for each connection, and shows the kept list at once when you connect. Each time it shows a list, it also asks the server again in the background, at most once every 30 seconds for that list, and updates the Explorer when something changed.

• If you created or dropped something in another tool and the Explorer does not show it yet, click Refresh objects at the top of the Explorer.
• The first connect to a database with thousands of tables takes longer, because nothing is kept yet. Later connects show the kept list first.
• To keep completion lists short on a database with many schemas, set Tables completion offers, in Settings under Code, to the schemas on the search path.

Everything is slow on one connection

When every action waits a little, the cost is the distance to the server. Each page, each count and each refresh is at least one round trip.

1. Right-click the connection in the launcher and choose Test connection…. The message says connected in and the time. A connect takes several round trips, so this is a few times the delay each request pays. Tens of milliseconds is a nearby server. Hundreds means you feel it on every page.
2. An SSH tunnel adds the trip to the bastion and back. Connect directly where the network allows it.
3. A VPN, or a database in another region, adds the same delay to every request. A larger Rows per page helps scrolling there.

Behind a connection pooler

Behind a pooler in transaction mode, Alcuin cannot keep a result open on the server between pages, so it reads each result in one go, up to 100,000 rows or 32 MB. Read the rest says it can’t read more there, then stays grayed out, with that reason as its tip. To page through more, add a LIMIT, or connect directly or in session mode. Settings Alcuin makes per session, such as its statement time limit, may also not hold. See Connection poolers.

The app itself stops answering

If the window stops responding, or a banner says The engine stopped., the problem is in Alcuin, not the server. Click Restart engine, or see ALC-9004: Service unavailable. If it happens again, save a diagnostics bundle and report it.

What good looks like

The first page of a table shows within a moment of opening it, scrolling keeps up, and the Explorer shows your objects as soon as you connect. When a query is still slow after this, the work is on the server: Find out why something is slow is the next step.