Name: System Design Masterclass
Availability: InStock

A terminal capture of the views lab. Section one shows the query written out at 0.093 ms and the same query through the view at 0.082 ms, then reports that the two plans are identical character for character, and prints a plan using a bitmap heap scan on orders with a recheck condition on customer_id. Section two shows the aggregate at 94.929 ms, the materialised view at 0.017 ms and one REFRESH at 104 ms. Section three shows a plain view at 0.116 ms and a view containing a window function at 39.483 ms, with a plan showing a Subquery Scan on ranked_orders with the filter on top of it. — terminal-figureFigure 2. The measurement. A real PostgreSQL, two million rows, three questions
Every timing is the execution time reported by EXPLAIN ANALYZE against a database this lab started and tore down.

A hand-drawn figure. Two boxes at the top, one reading SELECT from the query written out with a WHERE on customer_id, the other reading SELECT from paid_orders with the same WHERE. Both connect down to a single green box reading one plan, character for character. A note reads that the view is substituted into the statement before planning, so by the time anything is optimised there is no view left. — SketchFigure 3. The two statements produced the same plan, character for character
That is a stronger claim than the two timings being close, and it is what the lab actually checked.

Compare plans, not timings, whenever the claim is that two things are the same query. A timing tells you they were similar on one machine on one afternoon; an identical plan tells you the database made no distinction at all.

A sequence diagram between the query, the view and the table. The query sends WHERE customer_id equals 12345, the view's definition mentions no customer_id at all, and the predicate arrives at the scan anyway, producing an index scan at 0.082 ms, noted that the filter was pushed through the rewrite. — SequenceFigure 4. A filter on a view reaches the index underneath it
Because the view is pasted in before planning, the optimiser sees one query over one table with two conditions on it, and is free to use whatever index serves them best.

This is why views are usually free. They are not a layer the query passes through, they are a piece of text that stops existing before any decision is made.

Database Views

The rest of this lesson

Continue this lesson

Two Features Whose Names Differ By One Word

What A View Actually Is