PostgreSQL plan flipper

01_plan_flipper.sql

-- This sample reproduces a bug of a "sudden" flip of a server-prepared statement in PG 9.2+
-- In this particular case, backend selects very bad index even though bind values are the same and the table is analyzed

TL;DR: if you want to know true execution plan for a query with bind variables, you must use "prepare...";
       then repeat "explain analyze execute" 6 times :)

-- Note: do not confuse "5 executions before pgjdbc starts to use server-prepared statements" with
--       "first 5 executions of a server-prepared statement before plan flip"

-- Here's discussion in pgsql-hackers list:
--   http://www.postgresql.org/message-id/CAB=Je-G_oBsdrjWuPNsb0jcQ9X1A9jygCnKZW2izejoYG5YoeA@mail.gmail.com
--   If you try that, please comment if that improves performance for you.

-- It seems just passing "boudParams" instead of NULL should fix the issue:
--    https://github.com/postgres/postgres/blob/ee943004466418595363d567f18c053bae407792/src/backend/utils/cache/plancache.c#L1152

-- Note: this example uses a single table to simplify the query. In real life, exactly the same issue happens
-- when joining several tables.

-- Tested in
--   PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
--   PostgreSQL 9.5rc1 on x86_64-apple-darwin15.2.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

drop table plan_flipper;

-- Create test table of 300 MiB
-- skewed column has 2 rows for each value on average, and 1'000'000 rows for value 0.
-- non_skewed column has always 20 rows for each value

create table plan_flipper(id int, skewed int, non_skewed int, padding varchar);

insert into plan_flipper(id, skewed, non_skewed, padding)
  select g.i as id, 0 as skewed, mod(g.i, 100000) as non_skewed, lpad(' ', 100, ' ')
    from generate_series(1,1000000) as g(i);

insert into plan_flipper(id, skewed, non_skewed, padding)
  select g.i as id, g.i as skewed, mod(g.i, 100000) as non_skewed, lpad(' ', 100, ' ')
    from generate_series(1000001,2000000) as g(i);

create index skewed__flipper ON plan_flipper(skewed);
create index non_skewed__flipper ON plan_flipper(non_skewed);
commit;
vacuum analyze plan_flipper;

deallocate flipper;

prepare flipper(int, int) as
  select * from plan_flipper where skewed = $1 and non_skewed = $2;

-- 1st
explain (ANALYZE, BUFFERS) execute flipper(0, 42);
explain (ANALYZE, BUFFERS) execute flipper(0, 42);
explain (ANALYZE, BUFFERS) execute flipper(0, 42);
explain (ANALYZE, BUFFERS) execute flipper(0, 42);
explain (ANALYZE, BUFFERS) execute flipper(0, 42);

-- Here is "the flipper" :)
explain (ANALYZE, BUFFERS) execute flipper(0, 42);

02_results_from_944.txt

1st-5th executions (as expected, PG picks non_skewed index)
"Index Scan using non_skewed__flipper on plan_flipper  (cost=0.43..42.77 rows=10 width=113) (actual time=0.030..0.072 rows=10 loops=1)"
"  Index Cond: (non_skewed = 42)"
"  Filter: (skewed = 0)"
"  Rows Removed by Filter: 10"
"  Buffers: shared hit=20 read=3"
"Execution time: 0.094 ms"

6th execution (backend picks skewed index):
"Index Scan using skewed__flipper on plan_flipper  (cost=0.43..6.77 rows=1 width=113) (actual time=0.067..355.867 rows=10 loops=1)"
"  Index Cond: (skewed = $1)"
"  Filter: (non_skewed = $2)"
"  Rows Removed by Filter: 999990"
"  Buffers: shared hit=18182 read=2735"
"Execution time: 355.901 ms"

03_results_from_95rc1.txt

1st
"Bitmap Heap Scan on plan_flipper  (cost=4.58..83.47 rows=10 width=113) (actual time=0.033..0.088 rows=10 loops=1)"
"  Recheck Cond: (non_skewed = 42)"
"  Filter: (skewed = 0)"
"  Rows Removed by Filter: 10"
"  Heap Blocks: exact=20"
"  Buffers: shared hit=8 read=15"
"  ->  Bitmap Index Scan on non_skewed__flipper  (cost=0.00..4.58 rows=20 width=0) (actual time=0.021..0.021 rows=20 loops=1)"
"        Index Cond: (non_skewed = 42)"
"        Buffers: shared read=3"
"Execution time: 0.106 ms"

2nd
"Bitmap Heap Scan on plan_flipper  (cost=4.58..83.47 rows=10 width=113) (actual time=0.018..0.035 rows=10 loops=1)"
"  Recheck Cond: (non_skewed = 42)"
"  Filter: (skewed = 0)"
"  Rows Removed by Filter: 10"
"  Heap Blocks: exact=20"
"  Buffers: shared hit=23"
"  ->  Bitmap Index Scan on non_skewed__flipper  (cost=0.00..4.58 rows=20 width=0) (actual time=0.012..0.012 rows=20 loops=1)"
"        Index Cond: (non_skewed = 42)"
"        Buffers: shared hit=3"
"Execution time: 0.053 ms"

6th
"Index Scan using skewed__flipper on plan_flipper  (cost=0.43..10.79 rows=1 width=113) (actual time=0.062..260.546 rows=10 loops=1)"
"  Index Cond: (skewed = $1)"
"  Filter: (non_skewed = $2)"
"  Rows Removed by Filter: 999990"
"  Buffers: shared hit=10 read=20907 written=13966"
"Execution time: 260.577 ms"

Update: PostgreSQL 12+ has plan_cache_mode=auto | force_custom_plan | force_generic_plan configuration setting to force custom plan: postgres/postgres@f7cb284
What it does it forces prepared statements to force the mode at execution time, and the setting is global for all the statements.