[Postgres-xl-general] Failed to close remote subplan
Mason Sharp
msharp at translattice.com
Tue Sep 23 10:30:38 PDT 2014
On Tue, Sep 23, 2014 at 11:41 AM, Aaron Jackson <ajackson at revionics.com>
wrote:
> The table in question is structured something like this:
>
> create table client.sales(
> storeid int,
> productid int,
> revenue numeric
> ) with( OIDS = false ) distribute by hash(storeid)
>
> So, I decided to try to aggregate revenue by store rather than product
> with a limit. This works just fine, so I believe it may be related to
> performing a limited aggregation on a non-distributed column.
>
I am having trouble reproducing this.
test1=# create schema client;
CREATE SCHEMA
test1=# create table client.sales(
storeid int,
productid int,
revenue numeric
) with( OIDS = false ) distribute by hash(storeid);
CREATE TABLE
test1=# insert into client.sales select ceiling(2000 * random()),
ceiling(5000 * random()), generate_series(1,100000);
INSERT 0 100000
Then I could successfully run both of these queries:
SELECT s.productid, count(s.revenue) AS revenue FROM client.sales s group
by s.productid LIMIT 1000;
SELECT s.productid, sum(s.revenue) AS revenue FROM client.sales s group by
s.productid LIMIT 1000;
My explain plans look a little different though.
test1=# explain SELECT s.productid, count(s.revenue) AS revenue
FROM client.sales s group by s.productid LIMIT 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=2042.00..2052.00 rows=1000 width=10)
-> HashAggregate (cost=2042.00..2066.97 rows=2497 width=10)
-> Remote Subquery Scan on all (dn1,dn2) (cost=0.00..1542.00
rows=100000 width=10)
-> HashAggregate (cost=0.00..1542.00 rows=2497 width=10)
-> Seq Scan on sales s (cost=0.00..1542.00
rows=100000 width=10)
(5 rows)
I am just running two datanodes for this though, not 6 like you.
Does this above work for you ok?
Is there anything in the datanode log files? Like hitting some resource
maximum?
Also, I don't think it would impact you, but have you increased
shared_buffers and work_mem from the defaults in the datanodes?
Thanks,
Mason
>
> *Aaron*
> ------------------------------
> *From:* Aaron Jackson [ajackson at revionics.com]
> *Sent:* Tuesday, September 23, 2014 10:15 AM
> *To:* postgres-xl-general at lists.sourceforge.net
> *Subject:* Re: [Postgres-xl-general] Failed to close remote subplan
>
> I've made a smallish replica table client.sales with a productid and
> revenue. The idea is to summarize revenue by product...
>
> EXPLAIN SELECT s.productid, sum(s.revenue) AS revenue FROM client.sales
> s group by s.productid LIMIT 1000;
>
> Limit (cost=13820090.77..13833634.87 rows=1000 width=10)
> -> GroupAggregate (cost=13820090.77..14297425.44 rows=35243
> width=10)
> -> Remote Subquery Scan on all
> (datanode1,datanode2,datanode3,datanode4,datanode5,datanode6) (cost=1382
> * 0090.77..13979084.85 rows=63597632 width=10) *
> -> GroupAggregate (cost=13820090.77..13979084.85
> rows=35243 width=10)
> -> Sort (cost=13820090.77..13979084.85
> rows=63597632 width=10)
> Sort Key: productid
> -> Seq Scan on sales s
> (cost=0.00..1229548.32 rows=63597632 width=10)
>
> Wanted to compare that to a similar non-aggregate query.. which works.
>
> EXPLAIN SELECT s.productid, count(s.revenue) AS revenue FROM
> client.sales s group by s.productid LIMIT 1000;
>
> Limit (cost=13820090.77..13833634.87 rows=1000 width=10)
> -> GroupAggregate (cost=13820090.77..14297425.44 rows=35243 width=10)
> -> Remote Subquery Scan on all
> (datanode1,datanode2,datanode3,datanode4,datanode5,datanode6) (cost=13820090.77..13979084.85
> rows=63597632 width=10)
> -> GroupAggregate (cost=13820090.77..13979084.85
> rows=35243 width=10)
> -> Sort (cost=13820090.77..13979084.85
> rows=63597632 width=10)
> Sort Key: productid
> -> Seq Scan on sales s
> (cost=0.00..1229548.32 rows=63597632 width=10)
>
> And to verify that this fails on aggregates, I did an average as well.
>
> SELECT s.productid, avg(s.revenue) AS revenue FROM client.sales s group
> by s.productid LIMIT 10;
> ERROR: Failed to close remote subplan
>
> Note that I also adjusted the limit down to see if that was the
> bottleneck. Then I eliminated the limit entirely.
>
> SELECT s.productid, sum(s.revenue) AS revenue FROM client.sales s group
> by s.productid;
> SELECT s.productid, avg(s.revenue) AS revenue FROM client.sales s group
> by s.productid;
>
> Both queries without the limit clause succeeded.
>
> *Aaron*
> ------------------------------
> *From:* Aaron Jackson [ajackson at revionics.com]
> *Sent:* Monday, September 22, 2014 3:21 PM
> *To:* postgres-xl-general at lists.sourceforge.net
> *Subject:* [Postgres-xl-general] Failed to close remote subplan
>
> I promise to stop throwing out questions... soon!
>
> I have a case where most queries work just fine, however, the following
> query fails every time, with the message "ERROR: Failed to close remote
> subplan".
>
> SELECT s.productid, sum(s.value) AS revenue FROM ns.summary s group by
> s.productid LIMIT 1000
>
> If I modify the query every so slightly, it's fine...
>
> SELECT s.productid, count(s.value) AS revenue FROM ns.summary s group by
> s.productid LIMIT 1000
>
> The coordinator (DEBUG1) logs "timeout while waiting for response" -
> which corresponds to the post-timeout in pgxc_node_receive. Unfortunately,
> the datanodes don't seem to have much information. Anyone have a
> specific suggestion about where to look to see what the remote data node
> did not respond.
>
> *Aaron*
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xl-general mailing list
> Postgres-xl-general at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-general
>
>
--
Mason Sharp
TransLattice - http://www.translattice.com
Clustered and Distributed Database Solutions
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20140923/b4ef50a1/attachment.htm>
More information about the postgres-xl-general
mailing list