Explain [Analyze] produces parallel scan for select Into table statements.

From: Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: Explain [Analyze] produces parallel scan for select Into table statements.
Date: 2016-03-09 14:48:45
Message-ID: CAD__OuiVUhY9_K-ED_Yqbd=_P4_DRxZL=CxK6fK1CcOdrjWXfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi All,

Explain [Analyze] Select Into table..... produces the plan which uses
parallel scans.

*Test:*

create table table1 (n int);
insert into table1 values (generate_series(1,5000000));
analyze table1;

set parallel_tuple_cost=0;

set max_parallel_degree=3;

postgres=# explain select into table2 from table1;

QUERY PLAN

-------------------------------------------------------------------------------

Gather (cost=1000.00..39253.03 rows=5000000 width=0)

Number of Workers: 3

-> Parallel Seq Scan on table1 (cost=0.00..38253.03 rows=1612903
width=0)

(3 rows)

-----------------------------

*So Explain Analyze Fails.*

postgres=# explain analyze select into table2 from table1;

ERROR: cannot insert tuples during a parallel operation

STATEMENT: explain analyze select into table2 from table1;

*But actual execution is successful.*

postgres=# select into table2 from table1;

SELECT 5000000

Reason is in ExplainOneQuery we unconditionally

pass CURSOR_OPT_PARALLEL_OK to pg_plan_query even if query might be from

CreateTableAs/ SelectInto. Whereas in *ExecCreateTableAs *it is always 0*.*

*Possible Fix:*

I tried to make a patch to fix this. Now in ExplainOneQuery if into clause
is

defined then parallel plans are disabled as similar to their execution.
--
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com

Attachment Content-Type Size
Analyze_select_into_disable_parallel_scan.patch application/octet-stream 690 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Aleksander Alekseev 2016-03-09 14:54:01 Re: OOM in libpq and infinite loop with getCopyStart()
Previous Message Tom Lane 2016-03-09 14:47:56 Re: WIP: Upper planner pathification