-- This sample reproduces a bug of "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

-- 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 10 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;

explain (ANALYZE, BUFFERS) execute flipper(0, 42);

9.4.4:

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"


9.5rc1:

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"