We can inspect how the query planner will behave by investigating the EXPLAIN command. The explain command is a good way to help you understand the type of join algorithm that will be used, the types of scans that the search algorithms will use, and what indices will be used (or temporarily created) for the search. We will not go into much detail here, but we will look at a few examples so that you can dive in later if you are doing some query optimization in your own work. Today, we will use the "EXPLAIN ANALYZE" option which not only plans, but also executes the query so that we can see actual times.
We will use the join query from the previous section.
EXPLAIN ANALYZE
SELECT *
FROM events LEFT JOIN venues ON events.venue_id = venues.venue_id;
And we get the output:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Hash Join (cost=11.51..33.81 rows=8 width=688) (actual time=0.122..0.235 rows=1 loops=1)
Hash Cond: (events.venue_id = venues.venue_id)
-> Seq Scan on events (cost=0.00..19.70 rows=970 width=56) (actual time=0.007..0.094 rows=7 loops=1)
-> Hash (cost=11.50..11.50 rows=1 width=632) (actual time=0.033..0.039 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on venues (cost=0.00..11.50 rows=1 width=632) (actual time=0.006..0.019 rows=1 loops=1)
Filter: ((name)::text = 'Crystal Ballroom'::text)
Rows Removed by Filter: 2
Planning Time: 0.068 ms
Execution Time: 0.317 ms
(10 rows)
In class, I will dissect what this output means.