Обсуждение: EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

Hi,

I found $SUBJECT while trying to test parallel queries. Is this a bug?


In not parallel mode, EXPLAIN ANALYZE reports the information about
Sort Method as follows.

=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;
 QUERY PLAN
 

-----------------------------------------------------------------------------------------------------------------------------------Sort
(cost=180739.34..183239.34 rows=1000000 width=97) (actual
 
time=1501.342..1836.057 rows=1000000 loops=1)  Sort Key: bid  Sort Method: external sort  Disk: 104600kB  ->  Seq Scan
onpgbench_accounts  (cost=0.00..26394.00 rows=1000000
 
width=97) (actual time=0.013..179.315 rows=1000000 loops=1)


However, in parallel mode, it's not reported, as follows.

=# SET force_parallel_mode TO on;
=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;
    QUERY
 
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------Gather
(cost=181739.34..284239.34 rows=1000000 width=97) (actual
 
time=1507.138..2394.028 rows=1000000 loops=1)  Workers Planned: 1  Workers Launched: 1  Single Copy: true  ->  Sort
(cost=180739.34..183239.34rows=1000000 width=97) (actual
 
time=1503.112..1901.117 rows=1000000 loops=1)        Sort Key: bid        ->  Seq Scan on pgbench_accounts
(cost=0.00..26394.00
rows=1000000 width=97) (actual time=0.021..181.079 rows=1000000
loops=1)

Regards,

-- 
Fujii Masao

On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> Hi,
>
> I found $SUBJECT while trying to test parallel queries. Is this a bug?
>
>
> In not parallel mode, EXPLAIN ANALYZE reports the information about
> Sort Method as follows.
>
> =# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;
>                                                             QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=180739.34..183239.34 rows=1000000 width=97) (actual
> time=1501.342..1836.057 rows=1000000 loops=1)
>    Sort Key: bid
>    Sort Method: external sort  Disk: 104600kB
>    ->  Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000
> width=97) (actual time=0.013..179.315 rows=1000000 loops=1)
>
>
> However, in parallel mode, it's not reported, as follows.
>
> =# SET force_parallel_mode TO on;
> =# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;
>                                                                QUERY
> PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------
>  Gather  (cost=181739.34..284239.34 rows=1000000 width=97) (actual
> time=1507.138..2394.028 rows=1000000 loops=1)
>    Workers Planned: 1
>    Workers Launched: 1
>    Single Copy: true
>    ->  Sort  (cost=180739.34..183239.34 rows=1000000 width=97) (actual
> time=1503.112..1901.117 rows=1000000 loops=1)
>          Sort Key: bid
>          ->  Seq Scan on pgbench_accounts  (cost=0.00..26394.00
> rows=1000000 width=97) (actual time=0.021..181.079 rows=1000000
> loops=1)
>

I think this can never happen for force_parallel_mode TO off, because
we don't generate a gather on top of sort node.  The reason why we are
able to push Sort below gather, because it is marked as parallel_safe
(create_sort_path).  I think we should not mark it as parallel_safe.
Will investigate some more and send a patch.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Amit Kapila <amit.kapila16@gmail.com> writes:
> On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> I found $SUBJECT while trying to test parallel queries. Is this a bug?

Presumably the instrumentation data needed for that is not getting
returned from the worker to the leader.  I would bet there's a lot
of other plan-node-specific data that doesn't work either.

> I think this can never happen for force_parallel_mode TO off, because
> we don't generate a gather on top of sort node.  The reason why we are
> able to push Sort below gather, because it is marked as parallel_safe
> (create_sort_path).  I think we should not mark it as parallel_safe.

That seems rather ridiculous.  An oversight in managing EXPLAIN data
is not a sufficient reason to cripple parallel query.
        regards, tom lane

On Thu, Jul 7, 2016 at 7:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Kapila <amit.kapila16@gmail.com> writes:
>> On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>> I found $SUBJECT while trying to test parallel queries. Is this a bug?
>
> Presumably the instrumentation data needed for that is not getting
> returned from the worker to the leader.  I would bet there's a lot
> of other plan-node-specific data that doesn't work either.
>
>> I think this can never happen for force_parallel_mode TO off, because
>> we don't generate a gather on top of sort node.  The reason why we are
>> able to push Sort below gather, because it is marked as parallel_safe
>> (create_sort_path).  I think we should not mark it as parallel_safe.
>
> That seems rather ridiculous.  An oversight in managing EXPLAIN data
> is not a sufficient reason to cripple parallel query.
>

I am analyzing that point only and you seems to be right that we have
missed to propagate some information.  We have taken care of
instrumentation information to be propagated back to leader, but it
seems there are other things that needs to be taken care in that area.



-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Kapila <amit.kapila16@gmail.com> writes:
>> On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>> I found $SUBJECT while trying to test parallel queries. Is this a bug?
>
> Presumably the instrumentation data needed for that is not getting
> returned from the worker to the leader.

Yes.

> I would bet there's a lot
> of other plan-node-specific data that doesn't work either.

That's probably true, too.  Generally, what's going to happen here is
that if you have a true parallel query plan, any of this sort of
subsidiary information is going to reflect what the leader did, but
not what the workers did.  If the leader did nothing, as in the case
of force_parallel_mode, then EXPLAIN ANALYZE will show the same thing
that it would have shown if that node had never executed.

>> I think this can never happen for force_parallel_mode TO off, because
>> we don't generate a gather on top of sort node.  The reason why we are
>> able to push Sort below gather, because it is marked as parallel_safe
>> (create_sort_path).  I think we should not mark it as parallel_safe.
>
> That seems rather ridiculous.  An oversight in managing EXPLAIN data
> is not a sufficient reason to cripple parallel query.

+1.

Fixing this is actually somewhat difficult.  The parallel query stuff
does handle propagating the common instrumentation information from
the leader to the workers, but the EXPLAIN ANALYZE output can depend
in arbitrary ways on the final executor state tree, which is, of
course, unshared, and which is also not something we can propagate
between backends since executor state nodes don't have (and can't
really support) serialization and deserialization functions.  I think
we can eventually fix this by teaching individual nodes to store the
relevant information in dynamic shared memory rather than
backend-local memory when parallel query is in use: the
Estimate/InitializeDSM callbacks already give the nodes a chance to
obtain control in the right places, except that right now they're only
invoked for parallel-aware nodes.  I think, though, that it will take
more development than we want to undrertake at this point in the
cycle.

I'm not sure about the rest of you, but I'd kind of like to finish
this release and start working on the next one.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Presumably the instrumentation data needed for that is not getting
>> returned from the worker to the leader.

> Yes.

> ...
> I'm not sure about the rest of you, but I'd kind of like to finish
> this release and start working on the next one.

Agreed.  We should make sure that the possible omissions from EXPLAIN
output are adequately documented, but actually fixing that seems like
material for a future release cycle.
        regards, tom lane

On Fri, Jul 8, 2016 at 12:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Presumably the instrumentation data needed for that is not getting
>>> returned from the worker to the leader.
>
>> Yes.
>
>> ...
>> I'm not sure about the rest of you, but I'd kind of like to finish
>> this release and start working on the next one.
>
> Agreed.  We should make sure that the possible omissions from EXPLAIN
> output are adequately documented, but actually fixing that seems like
> material for a future release cycle.

Fair enough.

Regards,

-- 
Fujii Masao