What does this table do?

The symptom

Someone asks you to close out a batch of orders. You open commerce.orders, see status, placed_at and total_cents, and the UPDATE writes itself. What the rows don't show: who points at this table, what fires when you write to it, whether your WHERE clause has an index under it, and whether your role is allowed to do this at all.

What the explorer shows

Expand commerce.orders in the sidebar. Under it: Indexes, Constraints, Triggers and Access, plus Rules and Partitions when the table has any. Read them in this order.

The table tab and how many rows

Open the table. The status bar says of ~12M, and hovering over it reads "About 12,000,000 rows (catalog estimate)." That number is pg_class.reltuples, set by the last ANALYZE or VACUUM. orders was last analysed 41 days ago, so it is 41 days old. Click Count for a real count(*), which reads all 12,000,000 rows to get there.

Columns, with facts

Click a column header to select the column (only the header's sort button sorts) and press Space. The inspector shows what pg_stats knows about it: Most common values, the NULL share, and a Distribution across equal-count bands. On status it shows the five values and their shares. On customer_id it shows 398,000 distinct values. That says more than the type does: status is a small enum stored as text, customer_id is close to one per customer.

Select a single cell instead and In this table counts the loaded rows that share its value, with Count in table for the rest.

Constraints: who depends on this table

Open Constraints. The rail has three columns: Points at this table, Rules on its columns, Points to.

Points to lists crm.customers through customer_id and commerce.stores through store_id. These are the parents. An order cannot exist without its customer and its store.

Points at this table lists commerce.order_items through order_id and billing.invoices through order_id. These are the dependants. Deleting an order means PostgreSQL checks both, and each card says what a delete here does to the other side.

The middle column holds the primary key, the unique keys and the CHECK rules. Every rule holds is the verdict when nothing is NOT VALID. A constraint added NOT VALID gets its own row, because PostgreSQL enforces it for new rows and has never checked the old ones. Count rows that break it runs that check, and Copy VALIDATE gives you the statement that makes it a real constraint.

Indexes: how this table is queried

Open Indexes. Matrix shows one row per column and one column per index, with What it means at the end of each row. List shows one row per index with its size, scans, last use and a verdict: OK, Never used, Covered by <index>, Invalid or Building, and on MySQL and MariaDB Invisible or Ignored.

orders has three indexes: orders_pkey on id, orders_customer_placed_idx on (customer_id, placed_at DESC) and orders_status_placed_idx on (status, placed_at DESC). Read the matrix by row:

• customer_id and status each lead an index. A query that filters on either can be fast.
• placed_at sits at key position 2 in both. A query on placed_at alone can't use either, because a B-tree is ordered by its first column first.
• channel has no cell in any index. WHERE channel = 'web' reads the whole table. That is the slow query on Why is this query slow?.

The index set is the clearest record of how a table is used. Two indexes that lead with customer_id and status say the app looks orders up per customer and per status, newest first. Nobody built one for channel, so nobody queried it, until now.

A foreign key with no index gets a No index. note on its row and the cost: Deleting a customer scans all orders. orders is fine, customer_id leads an index. Why does this DELETE take three minutes? is about the table that isn't.

Triggers: what every write costs

Open Triggers. The page is five lanes in the order PostgreSQL runs them: Before the statement, Before each row, Instead of writing, After each row, After the statement. An empty lane says Nothing runs here.

orders has one trigger, orders_audit, in the After each row lane. It fires AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW and calls audit.log_change(), which writes to audit.change_log. Click it for the detail. This is the fact to know before an UPDATE: every row you change here writes a row there, with old_record and new_record as JSON. audit.change_log has 4,000,000 rows. Update 100,000 orders and it has 4,100,000.

Access: who may do what

Open Access. The matrix is roles against Read, Add, Change and Remove. Row security policies are drawn into the same matrix, because a grant and a policy together are what a role can actually see. Grants and policies agree is the verdict when they do. If your role has Read only, the UPDATE fails, and this page is the quicker way to learn that.

The DDL

At the bottom of each sub-page, folded: All properties and DDL. The DDL is the CREATE TABLE, with copy and open in console. Open it last. By now you can read it.

What is Free and what is Pro

The lists above are Free. The probes are Pro: counting the rows that break a NOT VALID constraint, estimating bloat. A probe on a Free install carries the Pro badge where it sits. Object pages shows what a Pro page looks like on Free.

Caveats

• of ~12M is a catalog estimate from the last ANALYZE or VACUUM. Count is exact.
• Column facts come from pg_stats, which is a sample. Most common values for status is reliable at five distinct values. For customer_id at 398,000 it is a sketch.
• MySQL and MariaDB have the Indexes, Constraints, Triggers and Access pages. Column facts come from MySQL 8 histograms, and MariaDB has none. SQLite has no Access or Partitions page. See MySQL, MariaDB and SQLite.
• Rules and Partitions only appear in the tree when the table has them.