Show all plans generated by the optimizer for a query, if more than one plan was considered by the optimizer
Tested with Postgres branch REL_17_STABLE(6526d07)
Example
Create table bar table with 2 columns(id and descr). There is an index on the id column. The table will have about 1M rows.
CREATE TABLE bar(id INT PRIMARY KEY, descr TEXT); -- New table
INSERT INTO bar SELECT i, 'Hello' FROM generate_series(100001, 1000000) AS i; -- populate table
Run EXPLAIN command to get the execution plan. Search for a random word in the descr field(this touches all the rows in the table since we are certain the search key does not exist)
postgres=# SELECT * FROM show_all_plans('EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM bar WHERE descr=''shoe''');
query_plans
-----------------------------------------------------------------------------------------------------------------
-------------------------------Plan 1-------------------------------
Gather (cost=1000.00..10633.40 rows=1 width=8) (actual time=47.286..48.178 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=480 read=3945
-> Parallel Seq Scan on bar (cost=0.00..9633.30 rows=1 width=8) (actual time=44.317..44.317 rows=0 loops=3)
Filter: (descr = 'shoe'::text)
Rows Removed by Filter: 333331
Buffers: shared hit=480 read=3945
Planning:
Buffers: shared hit=46
Planning Time: 0.237 ms
Execution Time: 48.212 ms
-------------------------------Plan 2-------------------------------
Seq Scan on bar (cost=0.00..16924.93 rows=1 width=8) (actual time=112.259..112.259 rows=0 loops=1)
Filter: (descr = 'shoe'::text)
Rows Removed by Filter: 999994
Buffers: shared hit=576 read=3849
Planning:
Buffers: shared hit=529 read=3945
Planning Time: 48.513 ms
Execution Time: 112.278 ms
(24 rows)
Two scan plans are considered, one involves a parallel sequential scan (with 2 workers) on the table while another is a normal sequential scan. In the end, only the parallel sequential scan is preferred since it deemed to provide the cheapest path.
In some cases, there is only a single path considered when the optimizer "thinks" that no other path could be cheaper than it, when all costs are taken into account(sort order, index, limit etc). Below is one such case where only the index scan plan is considered
postgres=# SELECT * FROM show_all_plans('EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM bar WHERE id=1000002');
query_plans
--------------------------------------------------------------------------------------------------------------
-------------------------------Plan 1-------------------------------
Index Scan using bar_pkey on bar (cost=0.42..8.44 rows=1 width=8) (actual time=0.926..0.927 rows=0 loops=1)
Index Cond: (id = 1000002)
Buffers: shared read=3
Planning:
Buffers: shared hit=39 read=20
Planning Time: 7.003 ms
Execution Time: 1.480 ms
(9 rows)
Storing multiple plans requires more memory resources. The optimizer opts to delete and release memory of paths when it deems such paths would never be chosen if there are other cheaper plans. In such cases, the pg_show_plans extension would not be able to show such plans.
In order to preserve such plans so they than can be shown by the extension, there are patch files in the patch directory. Apply the patch main when used with Postgres v17(REL_17_STABLE branch). This patch contains minimal changes to the codebase to prevent deletion of paths even if there are cheaper paths being considered. You get to see more plans as shown below
postgres=# CREATE TABLE foo(id1 INT, id2 INT, id3 INT, id4 INT, descr TEXT); -- new table
postgres=# INSERT INTO foo SELECT i, i*3, i+i, i*2, 'hello' || i FROM generate_series(1, 10000000) i; -- 10M records
postgres=# CREATE INDEX idx_id1_id2_id3 ON foo(id1, id2, id3);
postgres=# SELECT * FROM show_all_plans('EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo WHERE id1 > 1000');
query_plans
-----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------Plan 1-------------------------------
Seq Scan on foo (cost=0.00..198530.00 rows=9999027 width=28) (actual time=0.250..2603.390 rows=9999000 loops=1)
Filter: (id1 > 1000)
Rows Removed by Filter: 1000
Buffers: shared hit=16182 read=57348
Planning:
Buffers: shared hit=47 read=4 dirtied=3
Planning Time: 0.939 ms
Execution Time: 3263.912 ms
-------------------------------Plan 2-------------------------------
Index Scan using idx_id1_id2_id3 on foo (cost=0.43..498815.28 rows=9999027 width=28) (actual time=0.444..3309.395 rows=9999000 loops=1)
Index Cond: (id1 > 1000)
Buffers: shared hit=2 read=111835 written=8120
Planning:
Buffers: shared hit=16229 read=57352 dirtied=3
Planning Time: 0.947 ms
Execution Time: 3981.410 ms
-------------------------------Plan 3-------------------------------
Bitmap Heap Scan on foo (cost=231288.89..429813.47 rows=9999027 width=28) (actual time=1319.351..3678.529 rows=9999000 loops=1)
Recheck Cond: (id1 > 1000)
Rows Removed by Index Recheck: 35
Heap Blocks: exact=40497 lossy=33026
Buffers: shared read=111837
-> Bitmap Index Scan on idx_id1_id2_id3 (cost=0.00..228789.14 rows=9999027 width=0) (actual time=1301.783..1301.783 rows=9999000 loops=1)
Index Cond: (id1 > 1000)
Buffers: shared read=38314
Planning:
Buffers: shared hit=16231 read=169190 dirtied=3 written=8120
Planning Time: 0.951 ms
Execution Time: 4338.259 ms
-------------------------------Plan 4-------------------------------
Gather (cost=1000.00..1126516.03 rows=9999027 width=28) (actual time=0.524..1658.062 rows=9999000 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16228 read=57302
-> Parallel Seq Scan on foo (cost=0.00..125613.33 rows=4166261 width=28) (actual time=0.118..917.219 rows=3333000 loops=3)
Filter: (id1 > 1000)
Rows Removed by Filter: 333
Buffers: shared hit=16228 read=57302
Planning:
Buffers: shared hit=16231 read=281027 dirtied=3 written=8120
Planning Time: 0.952 ms
Execution Time: 2434.354 ms
(47 rows)
Four plans were considered, plain sequential scan plan wins(the cheapest) and is selected by the optimizer. One reason why sequential scan is chosen is due to potentially low number of blocks that would be read from disk. An index scan may require reading blocks in the index table(if not in buffer) then reading data from the heap(if data required is not in the index). Also, a large number of tuples would be returned by the query as we are filtering only 1000 records out of 10M. Indexes don't work well when a large set of data is required. When we limit the query to a smaller subset of the data, the optimizer rightfully picks an index plan. See below:
postgres=# SELECT * FROM show_all_plans('EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo WHERE id1 > 1000 AND id1 < 1500000');
query_plans
--------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------Plan 1-------------------------------
Index Scan using idx_id1_id2_id3 on foo (cost=0.43..187892.55 rows=1498365 width=28) (actual time=0.023..548.997 rows=1498999 loops=1)
Index Cond: ((id1 > 1000) AND (id1 < 1500000))
Buffers: shared hit=2 read=16768
Planning:
Buffers: shared read=4
Planning Time: 0.170 ms
Execution Time: 651.542 ms
-------------------------------Plan 2-------------------------------
Seq Scan on foo (cost=0.00..223530.00 rows=1498365 width=28) (actual time=0.204..2333.970 rows=1498999 loops=1)
Filter: ((id1 > 1000) AND (id1 < 1500000))
Rows Removed by Filter: 8501001
Buffers: shared hit=10696 read=62834
Planning:
Buffers: shared hit=2 read=16772
Planning Time: 0.174 ms
Execution Time: 2432.406 ms
-------------------------------Plan 3-------------------------------
Bitmap Heap Scan on foo (cost=38406.68..205106.49 rows=1498365 width=28) (actual time=236.667..539.696 rows=1498999 loops=1)
Recheck Cond: ((id1 > 1000) AND (id1 < 1500000))
Heap Blocks: exact=11023
Buffers: shared hit=171 read=16599
-> Bitmap Index Scan on idx_id1_id2_id3 (cost=0.00..38032.08 rows=1498365 width=0) (actual time=233.444..233.445 rows=1498999 loops=1)
Index Cond: ((id1 > 1000) AND (id1 < 1500000))
Buffers: shared hit=171 read=5576
Planning:
Buffers: shared hit=10698 read=79606
Planning Time: 0.182 ms
Execution Time: 638.010 ms
-------------------------------Plan 4-------------------------------
Gather (cost=1000.00..286866.50 rows=1498365 width=28) (actual time=0.518..2226.432 rows=1498999 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=11023 read=62507
-> Parallel Seq Scan on foo (cost=0.00..136030.00 rows=624319 width=28) (actual time=0.093..805.573 rows=499666 loops=3)
Filter: ((id1 > 1000) AND (id1 < 1500000))
Rows Removed by Filter: 2833667
Buffers: shared hit=11023 read=62507
Planning:
Buffers: shared hit=10869 read=96205
Planning Time: 0.182 ms
Execution Time: 2349.584 ms
(46 rows)
One interesting bit is the bitmap heap index scan plan has a substantially high startup time, hence falls behind the sequential scan even though the total cost of bitmap index plan is lower than that of sequential scan.
Ideally pg_show_plans should not be used in a production enviroment. It can be useful in situations where the optimizer picks a known less optimal plan then you can create a test environment and debug the query to find out why a sub-optimal plan was selected.