Re: Possible regression with gather merge.

Thanks for reporting, I am looking into this.

On Wed, Mar 22, 2017 at 11:51 AM, Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
wrote:

> Adding more rows to table make gather merge execution time very slow
> when compared to non-parallel plan we get after disabling gather
> merge.
>
> create table test as (select id, (random()*10000)::int as v1, random() as
> v2 from generate_series(1,100000000) id);
>
> postgres=# set max_parallel_workers_per_gather = default;
> SET
> postgres=# explain analyze select * from test order by v1, v2 limit 10;
>
> QUERY PLAN
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------
> Limit (cost=1858610.53..1858611.70 rows=10 width=16) (actual
> time=31103.880..31103.885 rows=10 loops=1)
> -> Gather Merge (cost=1858610.53..11581520.05 rows=83333406
> width=16) (actual time=31103.878..31103.882 rows=10 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -> Sort (cost=1857610.50..1961777.26 rows=41666703
> width=16) (actual time=30560.865..30561.046 rows=911 loops=3)
> Sort Key: v1, v2
> Sort Method: external merge Disk: 841584kB
> -> Parallel Seq Scan on test (cost=0.00..957208.03
> rows=41666703 width=16) (actual time=0.050..2330.275 rows=33333333
> loops=3)
> Planning time: 0.292 ms
> Execution time: 31502.896 ms
> (10 rows)
>
> postgres=# set max_parallel_workers_per_gather = 0;
> SET
> postgres=# explain analyze select * from test order by v1, v2 limit 10;
> QUERY
> PLAN
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> Limit (cost=3701507.83..3701507.85 rows=10 width=16) (actual
> time=13231.264..13231.266 rows=10 loops=1)
> -> Sort (cost=3701507.83..3951508.05 rows=100000088 width=16)
> (actual time=13231.261..13231.262 rows=10 loops=1)
> Sort Key: v1, v2
> Sort Method: top-N heapsort Memory: 25kB
> -> Seq Scan on test (cost=0.00..1540541.88 rows=100000088
> width=16) (actual time=0.045..6759.363 rows=100000000 loops=1)
> Planning time: 0.131 ms
> Execution time: 13231.299 ms
> (7 rows)
>
> On Wed, Mar 22, 2017 at 11:07 AM, Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
> wrote:
> > I accidently encountered a case where gather merge was picked as
> > default but disabling same by setting max_parallel_workers_per_gather
> > = 0; produced a non-parallel plan which was faster than gather merge,
> > but its cost is marked too high when compared to gather merge.
> >
> > I guess we need some cost adjustment is planner code.
> >
> > Test setting
> > =========
> > create table test as (select id, (random()*10000)::int as v1, random() as
> > v2 from generate_series(1,1000000) id);
> > create index test_v1_idx on test (v1);
> >
> >
> > Server setting is default.
> >
> >
> > postgres=# explain analyze select * from test order by v1, v2 limit 10;
> > QUERY
> > PLAN
> > ------------------------------------------------------------
> ------------------------------------------------------------
> ----------------
> > Limit (cost=19576.71..19577.88 rows=10 width=16) (actual
> > time=265.989..265.995 rows=10 loops=1)
> > -> Gather Merge (cost=19576.71..116805.80 rows=833334 width=16)
> > (actual time=265.987..265.992 rows=10 loops=1)
> > Workers Planned: 2
> > Workers Launched: 2
> > -> Sort (cost=18576.69..19618.36 rows=416667 width=16)
> > (actual time=250.202..250.424 rows=911 loops=3)
> > Sort Key: v1, v2
> > Sort Method: external merge Disk: 9272kB
> > -> Parallel Seq Scan on test (cost=0.00..9572.67
> > rows=416667 width=16) (actual time=0.053..41.397 rows=333333 loops=3)
> > Planning time: 0.193 ms
> > Execution time: 271.222 ms
> >
> > postgres=# set max_parallel_workers_per_gather = 0;
> > SET
> > postgres=# explain analyze select * from test order by v1, v2 limit 10;
> > QUERY PLAN
> > ------------------------------------------------------------
> -----------------------------------------------------------------
> > Limit (cost=37015.64..37015.67 rows=10 width=16) (actual
> > time=211.582..211.584 rows=10 loops=1)
> > -> Sort (cost=37015.64..39515.64 rows=1000000 width=16) (actual
> > time=211.581..211.582 rows=10 loops=1)
> > Sort Key: v1, v2
> > Sort Method: top-N heapsort Memory: 25kB
> > -> Seq Scan on test (cost=0.00..15406.00 rows=1000000
> > width=16) (actual time=0.085..107.522 rows=1000000 loops=1)
> > Planning time: 0.093 ms
> > Execution time: 211.608 ms
> > (7 rows)
> >
> >
> >
> > --
> > Thanks and Regards
> > Mithun C Y
> > EnterpriseDB: http://www.enterprisedb.com
>
>
>
> --
> Thanks and Regards
> Mithun C Y
> EnterpriseDB: http://www.enterprisedb.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Rushabh Lathia