Here's the simplified testcase:
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
It reproduces the problem in both 9.4.4 and 9.5rc1.
It is reproducible via both psql and pgjdbc.
I use a single table, however my production case includes a join of
two tables and the query is like
select ... from foo, bar where foo.skewed=? and bar.non_skewed=? and
foo.bar_id=bar.id
Note: my application _always_ sends *the same* *bad* value for skewed
column (it effectively is used as a filtering column in the particular
query).
Unfortunately, on 6th execution backend switches to the plan that uses
skewed index access.
Is it something that can be fixed/improved?
Good plan (the first 5 executions):
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
Bad plan (all the subsequent executions):
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
Vladimir