Vladimir Sitnikov wrote: > 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
Advertising
The problem is that the index "skewed__flipper" is more selective than "non_skewed__flipper" except when "skewed = 0", so the generic plan prefers it. I don't know if there is a good solution except disabling server prepared statements. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
