Will this index help?

The symptom

Why is this query slow? ended with ANALYZE commerce.orders. After it the planner sees about 206,880 web orders instead of 612, drops the Nested Loops for Hash Joins, and still reads commerce.orders with a Seq Scan, 12,000,000 rows for 206,880 kept, because nothing on the table starts with channel. The suggested fix is an index on (channel, placed_at DESC), on a table that is 1.5 GB of heap.

Two places to find out without building anything

Both use hypopg, a PostgreSQL extension that lets the planner see an index that does not exist, or stop seeing one that does, for your session only. Nothing is written and the query is not run.

The Plan tab. Open the plan, click the Seq Scan on orders node, and find suggested at the bottom of Indexes · orders, with the columns channel, placed_at and a Test button.

The inspector for Seq Scan on orders, before Test is clicked.

Seqora creates the hypothetical index, plans the same statement again with estimates only, and prints the result on the suggested line: Index Scan · cost 383,580 → 22,993. The first word is what the scan of orders would become. The two numbers are the cost of the whole plan before and after. The scan itself goes from 363,000 to 2,412.88, and the 20,000 that remains is the joins and the aggregate, which the index does not touch. The line is highlighted when the new cost is under 90% of the old. The Seq Scan on orders finding's Copy CREATE INDEX puts the real statement on your clipboard, with CONCURRENTLY.

The Index page. Open any index from the explorer (Pro). The section With and without this index takes a query, picked from Queries it can answer or typed in. Compare plans it twice, once as things are and once with this index hidden from the planner. The two plans sit side by side as With this index and Without it, under a headline that says how much cheaper the planner expects it to be with it, or The planner expects it to be cheaper without it, or About the same either way, or PostgreSQL doesn't use this index for this query when the plan with it ignores it. That last one is the headline you want before dropping an index.

What a cost is

A cost is the planner's own estimate of work, in units where reading one page in sequence is 1. It is not milliseconds, and two servers with different settings give different costs for the same plan. A cost is only useful next to another cost for the same query on the same server.

Read the size of the change, not the number. 363,000 to 2,412.88 is a drop of 150×. That is a different plan shape, a read of the whole table replaced by a walk down an index into a slice of it, and you will feel it. A drop from 363,000 to 340,000 is the same shape with a slightly different guess, and you will not. The estimate also depends on the statistics, which is why this page starts after ANALYZE. Test an index against stale statistics and you get a confident answer to the wrong question.

What the test cannot tell you

• The write cost. Every row inserted into orders is also written to every index on it, and now to one more. Updates that change channel or placed_at pay too. The Index page's Where the space goes section shows this as Each write pays for it, with one square per index the row lands in.
• The size. A hypothetical index has no size. The real one on (channel, placed_at DESC) has the same shape as orders_status_placed_idx on (status, placed_at DESC), which is 179 MB, so expect about that. orders is 1.5 GB of heap and 2.3 GB with its three indexes today.
• The build. CREATE INDEX on 12,000,000 rows takes minutes and, without CONCURRENTLY, blocks writes to the table while it runs. With CONCURRENTLY it takes longer and blocks nothing.
• Whether the server has hypopg. If it is not installed, Test prints Install the hypopg extension to test an index without building it. in place of the result, and the Index page's compare section offers Open CREATE EXTENSION in a console instead of Compare. Hiding an existing index uses hypopg_hide_index, which arrived in hypopg 1.4; an older hypopg gets Open ALTER EXTENSION in a console instead. Installing an extension usually needs a superuser.

What to do

Test it in the Plan tab, read the cost as a shape change, then build it with CONCURRENTLY at a quiet time. Capture the plan again after the build. If the Index Scan is there and the Seq Scan on orders finding is gone, you are done. What the index costs the table once it exists is in Reading the stats.

Caveats

• Test and the work_mem actions in the Plan tab are PostgreSQL only.
• With and without this index on MySQL, MariaDB and SQLite is partial. SQLite plans the query on an in-memory copy of the schema with the index dropped, and because SQLite plans have no costs the numbers are rows visited, from sqlite_stat1. MySQL, MariaDB and SQLite has the table.
• Both sides of every comparison are estimates. The query is not run, so a cost that changes is a change in the planner's opinion.
• The costs above come from a 12-million-row copy of the sample. A smaller copy gives smaller costs with the same ratio.