Mastering PostgreSQL 12 - Third Edition

Trying it all out

Now that all of the optimization options have been discussed, it is time to see what kind of execution plan is produced by PostgreSQL.

Let's try using the query with fully analyzed but empty tables first:

test=# explain SELECT * FROM v, c WHERE v.aid = c.cid AND cid = 4;                                                                                  QUERY PLAN                                       --------------------------------------------------------------------------------------- Nested Loop  (cost=12.77..74.50 rows=2197 width=12)   ->  Nested Loop  (cost=8.51..32.05 rows=169 width=8)         ->  Bitmap Heap Scan on a  (cost=4.26..14.95 rows=13 width=4)               Recheck Cond: (aid = 4)               ->  Bitmap Index Scan on idx_a  (cost=0.00..4.25 rows=13 width=0)                     Index Cond: (aid = 4)         ->  Materialize  (cost=4.26..15.02 rows=13 width=4)               ->  Bitmap Heap Scan on b  (cost=4.26..14.95 ...