On Sun, Apr 1, 2018 at 12:06 AM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote:
> On 03/31/2018 10:43 PM, Tomas Vondra wrote: > > ... > > But I'm pretty sure it may lead to surprising behavior - for example if > > you disable incremental sorts (enable_incrementalsort=off), the plan > > will switch to plain sort without the additional costs. So you'll get a > > cheaper plan by disabling some operation. That's surprising. > > > > To illustrate this is a valid issue, consider this trivial example: > > create table t (a int, b int, c int); > > insert into t select 10*random(), 10*random(), 10*random() > from generate_series(1,1000000) s(i); > > analyze t; > > explain select * from (select * from t order by a,b) foo order by a,b,c; > > QUERY PLAN > ------------------------------------------------------------------------ > Incremental Sort (cost=133100.48..264139.27 rows=1000000 width=12) > Sort Key: t.a, t.b, t.c > Presorted Key: t.a, t.b > -> Sort (cost=132154.34..134654.34 rows=1000000 width=12) > Sort Key: t.a, t.b > -> Seq Scan on t (cost=0.00..15406.00 rows=1000000 width=12) > (6 rows) > > set enable_incrementalsort = off; > > explain select * from (select * from t order by a,b) foo order by a,b,c; > QUERY PLAN > ------------------------------------------------------------------------ > Sort (cost=261402.69..263902.69 rows=1000000 width=12) > Sort Key: t.a, t.b, t.c > -> Sort (cost=132154.34..134654.34 rows=1000000 width=12) > Sort Key: t.a, t.b > -> Seq Scan on t (cost=0.00..15406.00 rows=1000000 width=12) > (5 rows) > > So the cost with incremental sort was 264139, and after disabling the > incremental cost it dropped to 263902. Granted, the difference is > negligible in this case, but it's still surprising. > > Also, it can be made much more significant by reducing the number of > prefix groups in the data: > > truncate t; > > insert into t select 1,1,1 from generate_series(1,1000000) s(i); > > analyze t; > > set enable_incrementalsort = on; > > explain select * from (select * from t order by a,b) foo order by a,b,c; > > QUERY PLAN > ------------------------------------------------------------------------ > Incremental Sort (cost=324165.83..341665.85 rows=1000000 width=12) > Sort Key: t.a, t.b, t.c > Presorted Key: t.a, t.b > -> Sort (cost=132154.34..134654.34 rows=1000000 width=12) > Sort Key: t.a, t.b > -> Seq Scan on t (cost=0.00..15406.00 rows=1000000 width=12) > (6 rows) > > So that's 263902 vs. 341665, yet we still prefer the incremental mode. Problem is well-defined, thank you. I'll check what can be done in this field today. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
