query planner made some choice • Won’t tell you about query performance being affected by another session • Won’t tell you about stuff happening outside the database (i.e., in the OS) • Won’t tell you about external environmental factors (i.e., network latency)
• Uses Table/Index Statistics • Stored in pg_statistic (don’t look there) • Can be viewable by looking pg_stats (for the adventurous) • Refreshed with ANALYZE (not to be confused with EXPLAIN ANALYZE) • Tuned by Configuration • enable_* parameters • *_cost parameters
row in outer table, scan for matching rows in the inner table • Fast to start, best for small tables • Merge Join • Zipper-operation on sorted data sets • Good for large tables • High startup cost if additional sort is required • Hash Join • Build hash of inner table values, scan outer table for matches • Only usable for equality conditions • High startup cost, but fast execution A B A A B B A
where aid < 1000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- --------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..47.87 rows=939 width=97) (actual time=0.371..0.721 rows=999 loops=1) Index Cond: (aid < 1000) Planning Time: 0.226 ms Execution Time: 0.815 ms (4 rows) postgres=# EXPLAIN ANALYZE SELECT aid FROM pgbench_accounts where aid < 1000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- --------- Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..28.87 rows=939 width=4) (actual time=0.022..0.169 rows=999 loops=1) Index Cond: (aid < 1000) Heap Fetches: 0 Planning Time: 0.161 ms Execution Time: 0.237 ms (5 rows)
row) postgres=# EXPLAIN SELECT * FROM pgbench_accounts WHERE aid < 1000; QUERY PLAN --------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..50.30 rows=1029 width=97) Index Cond: (aid < 1000) (2 rows postgres=# SET random_page_cost = 100; postgres=# EXPLAIN SELECT * FROM pgbench_accounts WHERE aid < 1000; QUERY PLAN ---------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..434.30 rows=1029 width=97) Index Cond: (aid < 1000) (2 rows) postgres=# SET random_page_cost = 1000; postgres=# EXPLAIN SELECT * FROM pgbench_accounts WHERE aid < 1000; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on pgbench_accounts (cost=0.00..2890.00 rows=1029 width=97) Filter: (aid < 1000) (2 rows)
• Can be chosen if query planner thinks it will retrieve many matching rows Index Scan • Scan all/some rows in index; look up rows in heap • Causes random seek Index Only Scan • Scan all/some rows in index • No need to look up rows in heap Bitmap Heap Scan • Scan index, building a bitmap of pages to visit • Look up only relevant pages in heap for rows
INDEX foo ON pgbench_history (aid); CREATE INDEX postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_history WHERE aid < 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Seq Scan on pgbench_history (cost=0.00..2346.00 rows=35360 width=50) (actual time=0.221..22.912 rows=170 loops=1) Filter: (aid < 100) Rows Removed by Filter: 159911 Planning Time: 0.610 ms Execution Time: 24.292 ms (6 rows) postgres=# ANALYZE; ANALYZE postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_history WHERE aid < 100; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Scan using foo on pgbench_history (cost=0.42..579.09 rows=153 width=50) (actual time=0.017..1.918 rows=170 loops=1) Index Cond: (aid < 100) Planning Time: 0.167 ms Execution Time: 3.507 ms (5 rows)
(aid, substring( fi ller,1,1)); postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid = 10000 AND left( fi ller,1) = 'b'; QUERY PLAN ------------------------------------------------------------------------- Bitmap Heap Scan on pgbench_history (cost=4.44..12.26 rows=1 width=47) Recheck Cond: (aid = 10000) Filter: ("left"(( fi ller)::text, 1) = 'b'::text) Heap Blocks: exact=2 -> Bitmap Index Scan on fi llertext_idx (cost=0.00..4.43 rows=2 width=0) Index Cond: (aid = 10000) (6 rows) postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid = 10000 AND substring(lower( fi ller),1,1) = 'b'; QUERY PLAN ----------------------------------------------------------------------------- Bitmap Heap Scan on pgbench_history (cost=4.44..12.26 rows=1 width=47) Recheck Cond: (aid = 10000) Filter: ("substring"(lower(( fi ller)::text), 1, 1) = 'b'::text) Heap Blocks: exact=2 -> Bitmap Index Scan on fi llertext_idx (cost=0.00..4.43 rows=2 width=0) Index Cond: (aid = 10000) (6 rows)
(aid, substring( fi ller,1,1)); postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid = 10000 AND left( fi ller,1) = 'b'; postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid = 10000 AND substring(lower( fi ller),1,1) = 'b'; postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid = 10000 AND substring( fi ller,1,1) = 'b'; QUERY PLAN --------------------------------------------------------------------------------------- Index Scan using fi llertext_idx on pgbench_history (cost=0.42..8.44 rows=1 width=47) Index Cond: ((aid = 10000) AND ("substring"(( fi ller)::text, 1, 1) = 'b'::text)) (2 rows)
AS SELECT * FROM pgbench_accounts WHERE aid = $1; • First 5 executions use a custom plan (taking into account $1) • After that, a generic plan is used (often not very efficient) • Can adjust plan_cache_mode in v. 12 and later • Join order • JIT (Just-In-Time Compilation) • from_collapse_limit/ join_collapse_limit • ORMs
Can do EXPLAIN ANALYZE (and BUFFERS, FORMAT, etc.) • Can even log trigger statistics and nested statements • Can be done on a per-session basis with LOAD auto_explain; • Creates additional I/O on disk