Re: parallel joins, and better parallel explain
| От | Amit Kapila |
|---|---|
| Тема | Re: parallel joins, and better parallel explain |
| Дата | |
| Msg-id | CAA4eK1+s3uD2G1WSkEAW_FZgp8jeYw3YcnvtuepLihE_e1D7Dw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: parallel joins, and better parallel explain (Robert Haas <robertmhaas@gmail.com>) |
| Ответы |
Re: parallel joins, and better parallel explain
(Dilip Kumar <dilipbalaut@gmail.com>)
|
| Список | pgsql-hackers |
On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > set enable_hashjoin=off;
> > set enable_mergejoin=off;
>
> [ ... ]
>
>
> > Now here the point to observe is that non-parallel case uses both less
> > Execution time and Planning time to complete the statement. There
> > is a considerable increase in planning time without any benefit in
> > execution.
>
> So, you forced the query planner to give you a bad plan, and then
> you're complaining that the plan is bad?
>
>
> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > set enable_hashjoin=off;
> > set enable_mergejoin=off;
>
> [ ... ]
>
>
> > Now here the point to observe is that non-parallel case uses both less
> > Execution time and Planning time to complete the statement. There
> > is a considerable increase in planning time without any benefit in
> > execution.
>
> So, you forced the query planner to give you a bad plan, and then
> you're complaining that the plan is bad?
>
Oh no, I wanted to check the behaviour of parallel vs. non-parallel
execution of joins. I think even if hash and merge join are set to
off, it should have picked up non-parallel NestLoop plan. In any case,
I have done some more investigation of the patch and found that even
without changing query planner related parameters, it seems to give
bad plans (as in example below [1]). I think here the costing of rework each
worker has to do seems to be missing which is causing bad plans to
be selected over good plans. Another point is that with patch, the number of
paths that we explore to get the cheapest path have increased, do you think
we should try to evaluate it? One way is we run some queries where there
are more number of joins and see the impact on planner time and other is we
try to calculate the increase in number of paths that planner explores.
[1] -
CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 10000000) g;
CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 3000000) g;
Analyze t1;
Analyze t2;
Non-parallel case
postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
-------------
Aggregate (cost=261519.93..261519.94 rows=1 width=0) (actual time=2779.965..2779.965 rows=1 loops=1)
-> Hash Join (cost=204052.91..261519.92 rows=1 width=0) (actual time=2017.241..2779.947 rows=101
loops=1)
Hash Cond: (t2.c1 = t1.c1)
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000 width=4) (actual time=0.073..393.479
rows=3000000 loops=1)
-> Hash (cost=204052.90..204052.90 rows=1 width=4) (actual time=2017.130..2017.130 rows=101
loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on t1 (cost=0.00..204052.90 rows=1 width=4) (actual time=0.038..2017.105
rows=101 loops=1)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 9999899
Planning time: 0.113 ms
Execution time: 2780.000 ms
(11 rows)
Parallel-case
set max_parallel_degree=4;
postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=100895.52..100895.53 rows=1 width=0) (actual time=67871.443..67871.443 rows=1 loops=1)
-> Gather (cost=1000.00..100895.52 rows=1 width=0) (actual time=0.653..67871.287 rows=101 loops=1)
Number of Workers: 4
-> Nested Loop (cost=0.00..99895.42 rows=1 width=0) (actual time=591.408..16455.731 rows=20 loops=5)
Join Filter: (t1.c1 = t2.c1)
Rows Removed by Join Filter: 60599980
-> Parallel Seq Scan on t1 (cost=0.00..45345.09 rows=0 width=4) (actual time=433.350..433.386 rows=20 loops=5)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 1999980
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000 width=4) (actual time=0.005..395.480 rows=3000000 loops=101)
Planning time: 0.114 ms
Execution time: 67871.584 ms
(12 rows)
Without patch, parallel case
set max_parallel_degree=4;
Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=103812.21..103812.22 rows=1 width=0) (actual time=1207.043..1207.043 rows=1 loops=1)
-> Hash Join (cost=46345.20..103812.21 rows=1 width=0) (actual time=428.632..1207.027 rows=101 loops=1)
Hash Cond: (t2.c1 = t1.c1)
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000 width=4) (actual time=0.034..375.670 rows=3000000 loops=1)
-> Hash (cost=46345.19..46345.19 rows=1 width=4) (actual time=428.557..428.557 rows=101 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Gather (cost=1000.00..46345.19 rows=1 width=4) (actual time=0.287..428.476 rows=101 loops=1)
Number of Workers: 4
-> Parallel Seq Scan on t1 (cost=0.00..45345.09 rows=1 width=4) (actual time=340.139..425.591 rows=20 loops=5)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 1999980
Planning time: 0.116 ms
Execution time: 1207.196 ms
(13 rows)
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: pg_stat_replication log positions vs base backups
Следующее
От: Robert HaasДата:
Сообщение: Re: Passing initially_valid values instead of !skip_validation to StoreRelCheck() in AddRelationNewConstraints()