postgres-xc-developers

[Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Hi,

I am a graduate student working on my research in parallel databases. I
would like to know how the postgres-xc coordinator works. I understand that
the datanodes run the query in parallel and the results are collected by
the coordinator which runs any more computation that is required or just
provides the output to the client that requested the query. I would like to
know whether the coordinator does this data collection from datanodes in a
sequential fashion?
For example, lets consider we want to run the query on table *table_x*
which is hash distributed among 10 datanodes,
*select count(*) from table_x;*
Each datanode will run the query and give their local counts and the
coordinator has to collect the individual counts and come up with the final
count before sending the output. Is the data collection process at the
coordinator done in a sequential fashion? I am actually looking to
introduce some kind of parallelism in this data collection if it is
sequential and do performance studies. Please clarify.

-- 
Thanks
Mani
Department of Computer Science
Virginia Tech

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Abbas B. <abb...@en...>]

On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian <ma...@vt...>
wrote:

> Hi,
>
> I am a graduate student working on my research in parallel databases. I
> would like to know how the postgres-xc coordinator works. I understand that
> the datanodes run the query in parallel and the results are collected by
> the coordinator which runs any more computation that is required or just
> provides the output to the client that requested the query. I would like to
> know whether the coordinator does this data collection from datanodes in a
> sequential fashion?
>

The coordinator uses multiplexed IO using select on all fds of datanodes.
For more details please see pgxc_node_receive function in pgxcnode.c. The
loop for reading data on all set fds is sequential, but the coordinator
does not wait for data from the datanode to which the coordinator had sent
the query first.


> For example, lets consider we want to run the query on table *table_x*
> which is hash distributed among 10 datanodes,
> *select count(*) from table_x;*
> Each datanode will run the query and give their local counts and the
> coordinator has to collect the individual counts and come up with the final
> count before sending the output. Is the data collection process at the
> coordinator done in a sequential fashion? I am actually looking to
> introduce some kind of parallelism in this data collection if it is
> sequential and do performance studies. Please clarify.
>

To improve performance of any system, first study the bottleneck, and
target to widen that. Have you done any study of Postgres-XC to find where
the performance bottleneck is?


>
> --
> Thanks
> Mani
> Department of Computer Science
> Virginia Tech
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Hi Abbas,

Thanks for the reply. I understand how pgxc_node_receive is working to
get incoming data from the datanodes. 
Yes, I have made experiments to study the postgres-xc system in a setup
with 12 datanodes and a coordinator and performed join, groupby and
order by operations on two tables with around 20 million and 10 million
records respectively. I have found that around 20-30% of the time is
spent in the 'FetchTuple' method which reads into the provided tupleslot
one datarow at a time from the combiner's buffer which is in turn filled
by the pgxc_node_receive method. 

Thanks

On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> 
> 
> On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
>         Hi,
>         
>         
>         I am a graduate student working on my research in parallel
>         databases. I would like to know how the postgres-xc
>         coordinator works. I understand that the datanodes run the
>         query in parallel and the results are collected by the
>         coordinator which runs any more computation that is required
>         or just provides the output to the client that requested the
>         query. I would like to know whether the coordinator does this
>         data collection from datanodes in a sequential fashion?
> 
> 
> The coordinator uses multiplexed IO using select on all fds of
> datanodes. For more details please see pgxc_node_receive function in
> pgxcnode.c. The loop for reading data on all set fds is sequential,
> but the coordinator does not wait for data from the datanode to which
> the coordinator had sent the query first.
>  
>         For example, lets consider we want to run the query on table
>         table_x which is hash distributed among 10 datanodes,
>         select count(*) from table_x;
>         Each datanode will run the query and give their local counts
>         and the coordinator has to collect the individual counts and
>         come up with the final count before sending the output. Is the
>         data collection process at the coordinator done in a
>         sequential fashion? I am actually looking to introduce some
>         kind of parallelism in this data collection if it is
>         sequential and do performance studies. Please clarify.
> 
> 
> To improve performance of any system, first study the bottleneck, and
> target to widen that. Have you done any study of Postgres-XC to find
> where the performance bottleneck is?
>  
>         
>         
>         -- 
>         Thanks
>         Mani
>         Department of Computer Science
>         Virginia Tech
>         
>         ------------------------------------------------------------------------------
>         
>         _______________________________________________
>         Postgres-xc-developers mailing list
>         Pos...@li...
>         https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>         
> 
> 
> 
> 
> -- 
> -- 
> Abbas
> Architect
> 
> 
> Ph: 92.334.5100153
> 
> Skype ID: gabbasb
> 
> www.enterprisedb.com
> 
> Follow us on Twitter
> @EnterpriseDB 
> 
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> 

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Abbas B. <abb...@en...>]

On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...>
wrote:

> Hi Abbas,
>
> Thanks for the reply. I understand how pgxc_node_receive is working to
> get incoming data from the datanodes.
> Yes, I have made experiments to study the postgres-xc system in a setup
> with 12 datanodes and a coordinator and performed join, groupby and
> order by operations on two tables with around 20 million and 10 million
> records respectively. I have found that around 20-30% of the time is
> spent in the 'FetchTuple' method which reads into the provided tupleslot
> one datarow at a time from the combiner's buffer which is in turn filled
> by the pgxc_node_receive method.
>

XC planner pushes parts of query processing to datanodes to speed things up.
In the queries you tried, did the query planner perform this optimization?
If not then you can gain advantage by improving the planner to generate
optimized plans that avoid UN-necessary materialization at the coordinator.
This will bring the FetchTuple time down since there will be less tuples to
fetch.

If the planner is already producing optimized plans, then what's your
proposal?



> Thanks
>
> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> >
> >
> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> >         Hi,
> >
> >
> >         I am a graduate student working on my research in parallel
> >         databases. I would like to know how the postgres-xc
> >         coordinator works. I understand that the datanodes run the
> >         query in parallel and the results are collected by the
> >         coordinator which runs any more computation that is required
> >         or just provides the output to the client that requested the
> >         query. I would like to know whether the coordinator does this
> >         data collection from datanodes in a sequential fashion?
> >
> >
> > The coordinator uses multiplexed IO using select on all fds of
> > datanodes. For more details please see pgxc_node_receive function in
> > pgxcnode.c. The loop for reading data on all set fds is sequential,
> > but the coordinator does not wait for data from the datanode to which
> > the coordinator had sent the query first.
> >
> >         For example, lets consider we want to run the query on table
> >         table_x which is hash distributed among 10 datanodes,
> >         select count(*) from table_x;
> >         Each datanode will run the query and give their local counts
> >         and the coordinator has to collect the individual counts and
> >         come up with the final count before sending the output. Is the
> >         data collection process at the coordinator done in a
> >         sequential fashion? I am actually looking to introduce some
> >         kind of parallelism in this data collection if it is
> >         sequential and do performance studies. Please clarify.
> >
> >
> > To improve performance of any system, first study the bottleneck, and
> > target to widen that. Have you done any study of Postgres-XC to find
> > where the performance bottleneck is?
> >
> >
> >
> >         --
> >         Thanks
> >         Mani
> >         Department of Computer Science
> >         Virginia Tech
> >
> >
>  ------------------------------------------------------------------------------
> >
> >         _______________________________________________
> >         Postgres-xc-developers mailing list
> >         Pos...@li...
> >
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> >
>
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Hi Abbas,

I just looked at the planner for the query that am running. This is the
query that am running,

SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d,
Table2 i WHERE d.id=i.id AND i.time BETWEEN
TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
ORDER BY d.col1 DESC;

And the query plan for a configuration with 4 datanodes and 1
coordinator,

GroupAggregate  (cost=0.07..0.10 rows=1 width=56)
   ->  Sort  (cost=0.07..0.08 rows=1 width=56)
         Sort Key: d.col1
         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)
               Hash Cond: (d.id = i.id)
               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=56)
                     Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)
                     ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=32)
                           Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
                           Coordinator quals: (((i.time)::double
precision <= trunc(((('now'::cstring)::date - to_date('01 Jan
2000'::text, 'DD Mon YYYY'::text)))::double precisio
n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date
- to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) -
365))::double precision)))


Table1 contains 22 million records and Table2 contains 10 million
records.
This query took several minutes (almost 35) to get results and one third
of that time is spent in FetchTuple. Do you see something wrong with the
query plan?

Since the multiplexed IO isn't the real bottleneck here, my proposal
would be to change the way how populating the TupleStore at the
coordinator is working and introduce parallelism there. Is this a right
direction? Please let me know your thoughts.

Thanks
Mani

On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
> 
> 
> 
> 
> On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
> 
>         Hi Abbas,
>         
>         Thanks for the reply. I understand how pgxc_node_receive is
>         working to
>         get incoming data from the datanodes.
>         Yes, I have made experiments to study the postgres-xc system
>         in a setup
>         with 12 datanodes and a coordinator and performed join,
>         groupby and
>         order by operations on two tables with around 20 million and
>         10 million
>         records respectively. I have found that around 20-30% of the
>         time is
>         spent in the 'FetchTuple' method which reads into the provided
>         tupleslot
>         one datarow at a time from the combiner's buffer which is in
>         turn filled
>         by the pgxc_node_receive method.
>         
> 
> 
> XC planner pushes parts of query processing to datanodes to speed
> things up.
> 
> In the queries you tried, did the query planner perform this
> optimization?
> 
> If not then you can gain advantage by improving the planner to
> generate optimized plans that avoid UN-necessary materialization at
> the coordinator.
> 
> This will bring the FetchTuple time down since there will be less
> tuples to fetch.
> 
> 
> If the planner is already producing optimized plans, then what's your
> proposal?
> 
> 
> 
>         
>         
>         Thanks
>         
>         
>         On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
>         >
>         >
>         > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
>         > <ma...@vt...> wrote:
>         >         Hi,
>         >
>         >
>         >         I am a graduate student working on my research in
>         parallel
>         >         databases. I would like to know how the postgres-xc
>         >         coordinator works. I understand that the datanodes
>         run the
>         >         query in parallel and the results are collected by
>         the
>         >         coordinator which runs any more computation that is
>         required
>         >         or just provides the output to the client that
>         requested the
>         >         query. I would like to know whether the coordinator
>         does this
>         >         data collection from datanodes in a sequential
>         fashion?
>         >
>         >
>         > The coordinator uses multiplexed IO using select on all fds
>         of
>         > datanodes. For more details please see pgxc_node_receive
>         function in
>         > pgxcnode.c. The loop for reading data on all set fds is
>         sequential,
>         > but the coordinator does not wait for data from the datanode
>         to which
>         > the coordinator had sent the query first.
>         >
>         >         For example, lets consider we want to run the query
>         on table
>         >         table_x which is hash distributed among 10
>         datanodes,
>         >         select count(*) from table_x;
>         >         Each datanode will run the query and give their
>         local counts
>         >         and the coordinator has to collect the individual
>         counts and
>         >         come up with the final count before sending the
>         output. Is the
>         >         data collection process at the coordinator done in a
>         >         sequential fashion? I am actually looking to
>         introduce some
>         >         kind of parallelism in this data collection if it is
>         >         sequential and do performance studies. Please
>         clarify.
>         >
>         >
>         > To improve performance of any system, first study the
>         bottleneck, and
>         > target to widen that. Have you done any study of Postgres-XC
>         to find
>         > where the performance bottleneck is?
>         >
>         >
>         >
>         >         --
>         >         Thanks
>         >         Mani
>         >         Department of Computer Science
>         >         Virginia Tech
>         >
>         >
>          ------------------------------------------------------------------------------
>         >
>         >         _______________________________________________
>         >         Postgres-xc-developers mailing list
>         >         Pos...@li...
>         >
>          https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>         >
>         >
>         >
>         >
>         >
>         > --
>         > --
>         > Abbas
>         > Architect
>         >
>         >
>         > Ph: 92.334.5100153
>         >
>         > Skype ID: gabbasb
>         >
>         > www.enterprisedb.com
>         >
>         > Follow us on Twitter
>         > @EnterpriseDB
>         >
>         > Visit EnterpriseDB for tutorials, webinars, whitepapers and
>         more
>         >
>         
>         
>         
> 
> 
> 
> 
> -- 
> 
> -- 
> Abbas
> 
> Architect
> 
> 
> Ph: 92.334.5100153
> 
> Skype ID: gabbasb
> 
> 
> www.enterprisedb.com
> 
> Follow us on Twitter
> @EnterpriseDB 
> 
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> 

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Abbas B. <abb...@en...>]

On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...>
wrote:

>  Hi Abbas,
>
> I just looked at the planner for the query that am running. This is the
> query that am running,
>
> *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2
> i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
> ORDER BY d.col1 DESC;*
>
> And the query plan for a configuration with 4 datanodes and 1 coordinator,
>
> *GroupAggregate  (cost=0.07..0.10 rows=1 width=56)*
> *   ->  Sort  (cost=0.07..0.08 rows=1 width=56)*
> *         Sort Key: d.col1*
> *         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)*
> *               Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)*
> *               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=56)*
> *                     Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> *               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)*
> *                     ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=32)*
> *                           Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> *                           Coordinator quals: (((i.time)::double
> precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text,
> 'DD Mon YYYY'::text)))::double precisio*
> *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date -
> to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))*
>
>
> Table1 contains 22 million records and Table2 contains 10 million records.
> This query took several minutes (almost 35) to get results and one third
> of that time is spent in FetchTuple. Do you see something wrong with the
> query plan?
>

Can you provide output of
\d+ Table1
\d+ Table2
I am interested in the distribution column of the tables.

Also please provide the output of EXPLAIN VERBOSE on the query.



> Since the multiplexed IO isn't the real bottleneck here, my proposal would
> be to change the way how populating the TupleStore at the coordinator is
> working and introduce parallelism there. Is this a right direction? Please
> let me know your thoughts.
>
> Thanks
> Mani
>
>
> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>
>
>
>  On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <
> ma...@vt...> wrote:
>
> Hi Abbas,
>
> Thanks for the reply. I understand how pgxc_node_receive is working to
> get incoming data from the datanodes.
> Yes, I have made experiments to study the postgres-xc system in a setup
> with 12 datanodes and a coordinator and performed join, groupby and
> order by operations on two tables with around 20 million and 10 million
> records respectively. I have found that around 20-30% of the time is
> spent in the 'FetchTuple' method which reads into the provided tupleslot
> one datarow at a time from the combiner's buffer which is in turn filled
> by the pgxc_node_receive method.
>
>
>  XC planner pushes parts of query processing to datanodes to speed things
> up.
>
>  In the queries you tried, did the query planner perform this
> optimization?
>
>  If not then you can gain advantage by improving the planner to generate
> optimized plans that avoid UN-necessary materialization at the coordinator.
>
>  This will bring the FetchTuple time down since there will be less tuples
> to fetch.
>
>
>  If the planner is already producing optimized plans, then what's your
> proposal?
>
>
>
>
> Thanks
>
>
> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> >
> >
> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> >         Hi,
> >
> >
> >         I am a graduate student working on my research in parallel
> >         databases. I would like to know how the postgres-xc
> >         coordinator works. I understand that the datanodes run the
> >         query in parallel and the results are collected by the
> >         coordinator which runs any more computation that is required
> >         or just provides the output to the client that requested the
> >         query. I would like to know whether the coordinator does this
> >         data collection from datanodes in a sequential fashion?
> >
> >
> > The coordinator uses multiplexed IO using select on all fds of
> > datanodes. For more details please see pgxc_node_receive function in
> > pgxcnode.c. The loop for reading data on all set fds is sequential,
> > but the coordinator does not wait for data from the datanode to which
> > the coordinator had sent the query first.
> >
> >         For example, lets consider we want to run the query on table
> >         table_x which is hash distributed among 10 datanodes,
> >         select count(*) from table_x;
> >         Each datanode will run the query and give their local counts
> >         and the coordinator has to collect the individual counts and
> >         come up with the final count before sending the output. Is the
> >         data collection process at the coordinator done in a
> >         sequential fashion? I am actually looking to introduce some
> >         kind of parallelism in this data collection if it is
> >         sequential and do performance studies. Please clarify.
> >
> >
> > To improve performance of any system, first study the bottleneck, and
> > target to widen that. Have you done any study of Postgres-XC to find
> > where the performance bottleneck is?
> >
> >
> >
> >         --
> >         Thanks
> >         Mani
> >         Department of Computer Science
> >         Virginia Tech
> >
> >
>  ------------------------------------------------------------------------------
> >
> >         _______________________________________________
> >         Postgres-xc-developers mailing list
> >         Pos...@li...
> >
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> >
>
>
>
>
>
>
> --
>
>  --
> *Abbas*
>
>  Architect
>
>
> Ph: 92.334.5100153
>
>  Skype ID: gabbasb
>
>  www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Ashutosh B. <ash...@en...>]

On Thu, Jun 4, 2015 at 6:38 AM, Abbas Butt <abb...@en...>
wrote:

>
>
> On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...>
> wrote:
>
>>  Hi Abbas,
>>
>> I just looked at the planner for the query that am running. This is the
>> query that am running,
>>
>> *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d,
>> Table2 i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN
>> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
>> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
>> ORDER BY d.col1 DESC;*
>>
>> And the query plan for a configuration with 4 datanodes and 1 coordinator,
>>
>> *GroupAggregate  (cost=0.07..0.10 rows=1 width=56)*
>> *   ->  Sort  (cost=0.07..0.08 rows=1 width=56)*
>> *         Sort Key: d.col1*
>> *         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)*
>> *               Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)*
>>
>
Are both the tables distributed by id?
The clauses with mutable functions like current_date are being pushded down
to individual relations. That prevents join from being pushed down to the
datanodes if id is distribution key. I think that should be fixed. The
clause movement should be reversed to the Join node.


> *               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
>> (cost=0.00..0.00 rows=1000 width=56)*
>> *                     Node/s: datanode_c2_d1, datanode_c3_d1,
>> datanode_c4_d1, datanode_c5_d1*
>> *               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)*
>> *                     ->  Data Node Scan on Table2
>> "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=32)*
>> *                           Node/s: datanode_c2_d1, datanode_c3_d1,
>> datanode_c4_d1, datanode_c5_d1*
>> *                           Coordinator quals: (((i.time)::double
>> precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text,
>> 'DD Mon YYYY'::text)))::double precisio*
>> *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date
>> - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
>> precision)))*
>>
>>
>> Table1 contains 22 million records and Table2 contains 10 million records.
>> This query took several minutes (almost 35) to get results and one third
>> of that time is spent in FetchTuple. Do you see something wrong with the
>> query plan?
>>
>
> Can you provide output of
> \d+ Table1
> \d+ Table2
> I am interested in the distribution column of the tables.
>
> Also please provide the output of EXPLAIN VERBOSE on the query.
>
>
>
>> Since the multiplexed IO isn't the real bottleneck here, my proposal
>> would be to change the way how populating the TupleStore at the coordinator
>> is working and introduce parallelism there. Is this a right direction?
>> Please let me know your thoughts.
>>
>> Thanks
>> Mani
>>
>>
>> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>>
>>
>>
>>  On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <
>> ma...@vt...> wrote:
>>
>> Hi Abbas,
>>
>> Thanks for the reply. I understand how pgxc_node_receive is working to
>> get incoming data from the datanodes.
>> Yes, I have made experiments to study the postgres-xc system in a setup
>> with 12 datanodes and a coordinator and performed join, groupby and
>> order by operations on two tables with around 20 million and 10 million
>> records respectively. I have found that around 20-30% of the time is
>> spent in the 'FetchTuple' method which reads into the provided tupleslot
>> one datarow at a time from the combiner's buffer which is in turn filled
>> by the pgxc_node_receive method.
>>
>>
>>  XC planner pushes parts of query processing to datanodes to speed
>> things up.
>>
>>  In the queries you tried, did the query planner perform this
>> optimization?
>>
>>  If not then you can gain advantage by improving the planner to generate
>> optimized plans that avoid UN-necessary materialization at the coordinator.
>>
>>  This will bring the FetchTuple time down since there will be less
>> tuples to fetch.
>>
>>
>>  If the planner is already producing optimized plans, then what's your
>> proposal?
>>
>>
>>
>>
>> Thanks
>>
>>
>> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
>> >
>> >
>> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
>> > <ma...@vt...> wrote:
>> >         Hi,
>> >
>> >
>> >         I am a graduate student working on my research in parallel
>> >         databases. I would like to know how the postgres-xc
>> >         coordinator works. I understand that the datanodes run the
>> >         query in parallel and the results are collected by the
>> >         coordinator which runs any more computation that is required
>> >         or just provides the output to the client that requested the
>> >         query. I would like to know whether the coordinator does this
>> >         data collection from datanodes in a sequential fashion?
>> >
>> >
>> > The coordinator uses multiplexed IO using select on all fds of
>> > datanodes. For more details please see pgxc_node_receive function in
>> > pgxcnode.c. The loop for reading data on all set fds is sequential,
>> > but the coordinator does not wait for data from the datanode to which
>> > the coordinator had sent the query first.
>> >
>> >         For example, lets consider we want to run the query on table
>> >         table_x which is hash distributed among 10 datanodes,
>> >         select count(*) from table_x;
>> >         Each datanode will run the query and give their local counts
>> >         and the coordinator has to collect the individual counts and
>> >         come up with the final count before sending the output. Is the
>> >         data collection process at the coordinator done in a
>> >         sequential fashion? I am actually looking to introduce some
>> >         kind of parallelism in this data collection if it is
>> >         sequential and do performance studies. Please clarify.
>> >
>> >
>> > To improve performance of any system, first study the bottleneck, and
>> > target to widen that. Have you done any study of Postgres-XC to find
>> > where the performance bottleneck is?
>> >
>> >
>> >
>> >         --
>> >         Thanks
>> >         Mani
>> >         Department of Computer Science
>> >         Virginia Tech
>> >
>> >
>>  ------------------------------------------------------------------------------
>> >
>> >         _______________________________________________
>> >         Postgres-xc-developers mailing list
>> >         Pos...@li...
>> >
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >
>> >
>> >
>> >
>> >
>> > --
>> > --
>> > Abbas
>> > Architect
>> >
>> >
>> > Ph: 92.334.5100153
>> >
>> > Skype ID: gabbasb
>> >
>> > www.enterprisedb.com
>> >
>> > Follow us on Twitter
>> > @EnterpriseDB
>> >
>> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
>> >
>>
>>
>>
>>
>>
>>
>> --
>>
>>  --
>> *Abbas*
>>
>>  Architect
>>
>>
>> Ph: 92.334.5100153
>>
>>  Skype ID: gabbasb
>>
>>  www.enterprisedb.co <http://www.enterprisedb.com/>m
>> <http://www.enterprisedb.com/>
>>
>> *Follow us on Twitter*
>> @EnterpriseDB
>>
>> Visit EnterpriseDB for tutorials, webinars, whitepapers
>> <http://www.enterprisedb.com/resources-community> and more
>> <http://www.enterprisedb.com/resources-community>
>>
>>
>>
>
>
> --
> --
> *Abbas*
>  Architect
>
> Ph: 92.334.5100153
>  Skype ID: gabbasb
> www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Yes, both the tables are distributed by 'id'.

On Thu, 2015-06-04 at 12:08 +0530, Ashutosh Bapat wrote:
> 
> 
> On Thu, Jun 4, 2015 at 6:38 AM, Abbas Butt
> <abb...@en...> wrote:
>         
>         
>         On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian
>         <ma...@vt...> wrote:
>                 Hi Abbas,
>                 
>                 I just looked at the planner for the query that am
>                 running. This is the query that am running,
>                 
>                 SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT
>                 FROM Table1 d, Table2 i WHERE d.id=i.id AND i.time
>                 BETWEEN TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD
>                 Mon YYYY')+1-365) AND TRUNC(CURRENT_DATE-TO_DATE('01
>                 Jan 2000','DD Mon YYYY')) GROUP BY d.col1 ORDER BY
>                 d.col1 DESC;
>                 
>                 And the query plan for a configuration with 4
>                 datanodes and 1 coordinator,
>                 
>                 GroupAggregate  (cost=0.07..0.10 rows=1 width=56)
>                    ->  Sort  (cost=0.07..0.08 rows=1 width=56)
>                          Sort Key: d.col1
>                          ->  Hash Join  (cost=0.01..0.06 rows=1
>                 width=56)
>                                Hash Cond: (d.id = i.id)
>                 
> 
> 
> Are both the tables distributed by id?
> 
> The clauses with mutable functions like current_date are being pushded
> down to individual relations. That prevents join from being pushed
> down to the datanodes if id is distribution key. I think that should
> be fixed. The clause movement should be reversed to the Join node.
> 
>  
>                                ->  Data Node Scan on Table1
>                 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000
>                 width=56)
>                                      Node/s: datanode_c2_d1,
>                 datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
>                                ->  Hash  (cost=0.00..0.00 rows=1000
>                 width=32)
>                                      ->  Data Node Scan on Table2
>                 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000
>                 width=32)
>                                            Node/s: datanode_c2_d1,
>                 datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
>                                            Coordinator quals:
>                 (((i.time)::double precision <=
>                 trunc(((('now'::cstring)::date - to_date('01 Jan
>                 2000'::text, 'DD Mon YYYY'::text)))::double precisio
>                 n)) AND ((i.time)::double precision >=
>                 trunc(((((('now'::cstring)::date - to_date('01 Jan
>                 2000'::text, 'DD Mon YYYY'::text)) + 1) -
>                 365))::double precision)))
>                 
>                 
>                 Table1 contains 22 million records and Table2 contains
>                 10 million records.
>                 This query took several minutes (almost 35) to get
>                 results and one third of that time is spent in
>                 FetchTuple. Do you see something wrong with the query
>                 plan?
>                 
>         
>         
>         Can you provide output of 
>         
>         \d+ Table1
>         \d+ Table2
>         
>         I am interested in the distribution column of the tables. 
>         
>         
>         Also please provide the output of EXPLAIN VERBOSE on the
>         query.
>         
>         
>         
>                 
>                 Since the multiplexed IO isn't the real bottleneck
>                 here, my proposal would be to change the way how
>                 populating the TupleStore at the coordinator is
>                 working and introduce parallelism there. Is this a
>                 right direction? Please let me know your thoughts.
>                 
>                 Thanks
>                 Mani
>                 
>                 
>                 On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote: 
>                 > 
>                 > 
>                 > On Tue, Jun 2, 2015 at 10:17 PM, Manikandan
>                 > Soundarapandian <ma...@vt...> wrote:
>                 >         Hi Abbas,
>                 >         
>                 >         Thanks for the reply. I understand how
>                 >         pgxc_node_receive is working to
>                 >         get incoming data from the datanodes.
>                 >         Yes, I have made experiments to study the
>                 >         postgres-xc system in a setup
>                 >         with 12 datanodes and a coordinator and
>                 >         performed join, groupby and
>                 >         order by operations on two tables with
>                 >         around 20 million and 10 million
>                 >         records respectively. I have found that
>                 >         around 20-30% of the time is
>                 >         spent in the 'FetchTuple' method which reads
>                 >         into the provided tupleslot
>                 >         one datarow at a time from the combiner's
>                 >         buffer which is in turn filled
>                 >         by the pgxc_node_receive method. 
>                 > 
>                 > XC planner pushes parts of query processing to
>                 > datanodes to speed things up.
>                 > 
>                 > In the queries you tried, did the query planner
>                 > perform this optimization?
>                 > 
>                 > If not then you can gain advantage by improving the
>                 > planner to generate optimized plans that avoid
>                 > UN-necessary materialization at the coordinator.
>                 > 
>                 > This will bring the FetchTuple time down since there
>                 > will be less tuples to fetch.
>                 > 
>                 > 
>                 > If the planner is already producing optimized plans,
>                 > then what's your proposal?
>                 > 
>                 > 
>                 > 
>                 >         
>                 >         Thanks 
>                 >         
>                 >         On Wed, 2015-06-03 at 09:45 +0500, Abbas
>                 >         Butt wrote:
>                 >         >
>                 >         >
>                 >         > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan
>                 >         Soundarapandian
>                 >         > <ma...@vt...> wrote:
>                 >         >         Hi,
>                 >         >
>                 >         >
>                 >         >         I am a graduate student working on
>                 >         my research in parallel
>                 >         >         databases. I would like to know
>                 >         how the postgres-xc
>                 >         >         coordinator works. I understand
>                 >         that the datanodes run the
>                 >         >         query in parallel and the results
>                 >         are collected by the
>                 >         >         coordinator which runs any more
>                 >         computation that is required
>                 >         >         or just provides the output to the
>                 >         client that requested the
>                 >         >         query. I would like to know
>                 >         whether the coordinator does this
>                 >         >         data collection from datanodes in
>                 >         a sequential fashion?
>                 >         >
>                 >         >
>                 >         > The coordinator uses multiplexed IO using
>                 >         select on all fds of
>                 >         > datanodes. For more details please see
>                 >         pgxc_node_receive function in
>                 >         > pgxcnode.c. The loop for reading data on
>                 >         all set fds is sequential,
>                 >         > but the coordinator does not wait for data
>                 >         from the datanode to which
>                 >         > the coordinator had sent the query first.
>                 >         >
>                 >         >         For example, lets consider we want
>                 >         to run the query on table
>                 >         >         table_x which is hash distributed
>                 >         among 10 datanodes,
>                 >         >         select count(*) from table_x;
>                 >         >         Each datanode will run the query
>                 >         and give their local counts
>                 >         >         and the coordinator has to collect
>                 >         the individual counts and
>                 >         >         come up with the final count
>                 >         before sending the output. Is the
>                 >         >         data collection process at the
>                 >         coordinator done in a
>                 >         >         sequential fashion? I am actually
>                 >         looking to introduce some
>                 >         >         kind of parallelism in this data
>                 >         collection if it is
>                 >         >         sequential and do performance
>                 >         studies. Please clarify.
>                 >         >
>                 >         >
>                 >         > To improve performance of any system,
>                 >         first study the bottleneck, and
>                 >         > target to widen that. Have you done any
>                 >         study of Postgres-XC to find
>                 >         > where the performance bottleneck is?
>                 >         >
>                 >         >
>                 >         >
>                 >         >         --
>                 >         >         Thanks
>                 >         >         Mani
>                 >         >         Department of Computer Science
>                 >         >         Virginia Tech
>                 >         >
>                 >         >
>                 >          ------------------------------------------------------------------------------
>                 >         >
>                 >         >
>                 >          _______________________________________________
>                 >         >         Postgres-xc-developers mailing
>                 >         list
>                 >         >
>                 >          Pos...@li...
>                 >         >
>                 >          https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>                 >         >
>                 >         >
>                 >         >
>                 >         >
>                 >         >
>                 >         > --
>                 >         > --
>                 >         > Abbas
>                 >         > Architect
>                 >         >
>                 >         >
>                 >         > Ph: 92.334.5100153
>                 >         >
>                 >         > Skype ID: gabbasb
>                 >         >
>                 >         > www.enterprisedb.com
>                 >         >
>                 >         > Follow us on Twitter
>                 >         > @EnterpriseDB
>                 >         >
>                 >         > Visit EnterpriseDB for tutorials,
>                 >         webinars, whitepapers and more
>                 >         >
>                 >         
>                 >         
>                 >         
>                 > 
>                 > 
>                 > 
>                 > -- 
>                 > -- 
>                 > Abbas
>                 > Architect
>                 > 
>                 > 
>                 > Ph: 92.334.5100153
>                 > 
>                 > Skype ID: gabbasb
>                 > 
>                 > www.enterprisedb.com
>                 > 
>                 > Follow us on Twitter
>                 > @EnterpriseDB 
>                 > 
>                 > Visit EnterpriseDB for tutorials, webinars,
>                 > whitepapers and more
>                 > 
>                 
>                 
>         
>         
>         
>         -- 
>         -- 
>         Abbas
>         Architect
>         
>         
>         Ph: 92.334.5100153
>         
>         Skype ID: gabbasb
>         
>         www.enterprisedb.com
>         
>         Follow us on Twitter
>         @EnterpriseDB 
>         
>         Visit EnterpriseDB for tutorials, webinars, whitepapers and
>         more
>         
>         
>         ------------------------------------------------------------------------------
>         
>         _______________________________________________
>         Postgres-xc-developers mailing list
>         Pos...@li...
>         https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>         
> 
> 
> 
> -- 
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
> 

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Hi, Please see below

\d+ Table1
Table "Table1"
    Column    |         Type          | Modifiers | Storage  | Stats
target | Description 
--------------+-----------------------+-----------+----------+--------------+-------------
 id          | numeric               |           | main     | 1000
| 
 age          | numeric               |           | main     | 1000
| 
 gender       | numeric               |           | main     | 1000
| 
 col1     | character varying(5)  |           | extended | 1000
| 
Indexes:
    "index_age_table1" btree (age)
    "index_col1_table1" btree (col1)
    "index_gender_table1" btree (gender)
    "index_id_table1" btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES

\d+ Table2
Table "Table2"
     Column      |  Type   | Modifiers | Storage | Stats target |
Description 
-----------------+---------+-----------+---------+--------------+-------------
 id             | numeric |           | main    | 1000         | 
 rep             | numeric |           | main    | 1000         | 
 time    | numeric |           | main    | 1000         | 
 
Indexes:
    "index_time_table2" btree (time)
    "index_id_table2" btree (id)
    "index_rep_table2" btree (rep)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES

Explain verbose of the query:
GroupAggregate  (cost=0.07..0.10 rows=1 width=56)
   Output: d.col1, round(((count(d.id) / 10))::double precision)
   ->  Sort  (cost=0.07..0.08 rows=1 width=56)
         Output: d.col1, d.id
         Sort Key: d.col1
         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)
               Output: d.col1, d.id
               Hash Cond: (d.id = i.id)
               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=56)
                     Output: d.col1, d.id
                     Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
                     Remote query: SELECT col1, id FROM ONLY Table1 d
WHERE true
               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)
                     Output: i.id
                     ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=32)
                           Output: i.id
                           Node/s: datanode_c2_d1, datanode_c3_d1,
datanode_c4_d1, datanode_c5_d1
                           Remote query: SELECT id, time FROM ONLY
Table2 i WHERE true
                           Coordinator quals: (((i.time)::double
precision <= trunc(((('now'::cstring)::date - to_date('01 Jan
2000'::text, 'DD Mon YYYY'::text)))::double precis
ion)) AND ((i.time)::double precision >=
trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon
YYYY'::text)) + 1) - 365))::double precision)))

Also, one weird thing that I noticed is if I don't give the 'trunc()'
date manipulation in the query and just gave some valid numbers in the
between clause like 'between 5266 and 5632', am getting a different
query plan and the query executes in just a few seconds. The following
is the query planner output when I give the numbers directly in the
between clause. 
GroupAggregate  (cost=49.83..57.35 rows=1 width=56)
   Output: d.col1, round(((count((count(d.id))) / 10))::double
precision)
   ->  Sort  (cost=49.83..52.33 rows=1000 width=56)
         Output: d.col1, (count(d.id))
         Sort Key: d.col1
         ->  Data Node Scan on "__REMOTE_GROUP_QUERY__"
(cost=0.00..0.00 rows=1000 width=56)
               Output: d.col1, (count(d.id))
               Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1,
datanode_c5_d1
               Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT
d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT
i.id FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <=
5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1
ORDER BY 1 DESC

But my requirement is with the trunc() date manipulation.

Thanks

On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:
> 
> 
> 
> 
> On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian
> <ma...@vt...> wrote:
> 
>         Hi Abbas,
>         
>         I just looked at the planner for the query that am running.
>         This is the query that am running,
>         
>         SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM
>         Table1 d, Table2 i WHERE d.id=i.id AND i.time BETWEEN
>         TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365)
>         AND TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY'))
>         GROUP BY d.col1 ORDER BY d.col1 DESC;
>         
>         And the query plan for a configuration with 4 datanodes and 1
>         coordinator,
>         
>         GroupAggregate  (cost=0.07..0.10 rows=1 width=56)
>            ->  Sort  (cost=0.07..0.08 rows=1 width=56)
>                  Sort Key: d.col1
>                  ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)
>                        Hash Cond: (d.id = i.id)
>                        ->  Data Node Scan on Table1
>         "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=56)
>                              Node/s: datanode_c2_d1, datanode_c3_d1,
>         datanode_c4_d1, datanode_c5_d1
>                        ->  Hash  (cost=0.00..0.00 rows=1000 width=32)
>                              ->  Data Node Scan on Table2
>         "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=32)
>                                    Node/s: datanode_c2_d1,
>         datanode_c3_d1, datanode_c4_d1, datanode_c5_d1
>                                    Coordinator quals:
>         (((i.time)::double precision <= trunc(((('now'::cstring)::date
>         - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double
>         precisio
>         n)) AND ((i.time)::double precision >=
>         trunc(((((('now'::cstring)::date - to_date('01 Jan
>         2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
>         precision)))
>         
>         
>         Table1 contains 22 million records and Table2 contains 10
>         million records.
>         This query took several minutes (almost 35) to get results and
>         one third of that time is spent in FetchTuple. Do you see
>         something wrong with the query plan?
>         
> 
> 
> Can you provide output of 
> 
> \d+ Table1
> \d+ Table2
> 
> I am interested in the distribution column of the tables. 
> 
> 
> Also please provide the output of EXPLAIN VERBOSE on the query.
> 
> 
> 
> 
> 
>         
>         Since the multiplexed IO isn't the real bottleneck here, my
>         proposal would be to change the way how populating the
>         TupleStore at the coordinator is working and introduce
>         parallelism there. Is this a right direction? Please let me
>         know your thoughts.
>         
>         Thanks
>         Mani
>         
>         
>         
>         On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote: 
>         
>         > 
>         > 
>         > On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian
>         > <ma...@vt...> wrote:
>         > 
>         >         Hi Abbas,
>         >         
>         >         Thanks for the reply. I understand how
>         >         pgxc_node_receive is working to
>         >         get incoming data from the datanodes.
>         >         Yes, I have made experiments to study the
>         >         postgres-xc system in a setup
>         >         with 12 datanodes and a coordinator and performed
>         >         join, groupby and
>         >         order by operations on two tables with around 20
>         >         million and 10 million
>         >         records respectively. I have found that around
>         >         20-30% of the time is
>         >         spent in the 'FetchTuple' method which reads into
>         >         the provided tupleslot
>         >         one datarow at a time from the combiner's buffer
>         >         which is in turn filled
>         >         by the pgxc_node_receive method. 
>         > 
>         > 
>         > XC planner pushes parts of query processing to datanodes to
>         > speed things up.
>         > 
>         > In the queries you tried, did the query planner perform this
>         > optimization?
>         > 
>         > If not then you can gain advantage by improving the planner
>         > to generate optimized plans that avoid UN-necessary
>         > materialization at the coordinator.
>         > 
>         > This will bring the FetchTuple time down since there will be
>         > less tuples to fetch.
>         > 
>         > 
>         > If the planner is already producing optimized plans, then
>         > what's your proposal?
>         > 
>         > 
>         > 
>         > 
>         >         
>         >         Thanks 
>         >         
>         >         On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
>         >         >
>         >         >
>         >         > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan
>         >         Soundarapandian
>         >         > <ma...@vt...> wrote:
>         >         >         Hi,
>         >         >
>         >         >
>         >         >         I am a graduate student working on my
>         >         research in parallel
>         >         >         databases. I would like to know how the
>         >         postgres-xc
>         >         >         coordinator works. I understand that the
>         >         datanodes run the
>         >         >         query in parallel and the results are
>         >         collected by the
>         >         >         coordinator which runs any more
>         >         computation that is required
>         >         >         or just provides the output to the client
>         >         that requested the
>         >         >         query. I would like to know whether the
>         >         coordinator does this
>         >         >         data collection from datanodes in a
>         >         sequential fashion?
>         >         >
>         >         >
>         >         > The coordinator uses multiplexed IO using select
>         >         on all fds of
>         >         > datanodes. For more details please see
>         >         pgxc_node_receive function in
>         >         > pgxcnode.c. The loop for reading data on all set
>         >         fds is sequential,
>         >         > but the coordinator does not wait for data from
>         >         the datanode to which
>         >         > the coordinator had sent the query first.
>         >         >
>         >         >         For example, lets consider we want to run
>         >         the query on table
>         >         >         table_x which is hash distributed among 10
>         >         datanodes,
>         >         >         select count(*) from table_x;
>         >         >         Each datanode will run the query and give
>         >         their local counts
>         >         >         and the coordinator has to collect the
>         >         individual counts and
>         >         >         come up with the final count before
>         >         sending the output. Is the
>         >         >         data collection process at the coordinator
>         >         done in a
>         >         >         sequential fashion? I am actually looking
>         >         to introduce some
>         >         >         kind of parallelism in this data
>         >         collection if it is
>         >         >         sequential and do performance studies.
>         >         Please clarify.
>         >         >
>         >         >
>         >         > To improve performance of any system, first study
>         >         the bottleneck, and
>         >         > target to widen that. Have you done any study of
>         >         Postgres-XC to find
>         >         > where the performance bottleneck is?
>         >         >
>         >         >
>         >         >
>         >         >         --
>         >         >         Thanks
>         >         >         Mani
>         >         >         Department of Computer Science
>         >         >         Virginia Tech
>         >         >
>         >         >
>         >          ------------------------------------------------------------------------------
>         >         >
>         >         >
>         >          _______________________________________________
>         >         >         Postgres-xc-developers mailing list
>         >         >
>         >          Pos...@li...
>         >         >
>         >          https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>         >         >
>         >         >
>         >         >
>         >         >
>         >         >
>         >         > --
>         >         > --
>         >         > Abbas
>         >         > Architect
>         >         >
>         >         >
>         >         > Ph: 92.334.5100153
>         >         >
>         >         > Skype ID: gabbasb
>         >         >
>         >         > www.enterprisedb.com
>         >         >
>         >         > Follow us on Twitter
>         >         > @EnterpriseDB
>         >         >
>         >         > Visit EnterpriseDB for tutorials, webinars,
>         >         whitepapers and more
>         >         >
>         >         
>         >         
>         >         
>         > 
>         > 
>         > 
>         > 
>         > -- 
>         > -- 
>         > Abbas
>         > Architect
>         > 
>         > 
>         > Ph: 92.334.5100153
>         > 
>         > Skype ID: gabbasb
>         > 
>         > www.enterprisedb.com
>         > 
>         > Follow us on Twitter
>         > @EnterpriseDB 
>         > 
>         > Visit EnterpriseDB for tutorials, webinars, whitepapers and
>         > more
>         > 
>         
>         
>         
> 
> 
> 
> 
> -- 
> 
> -- 
> Abbas
> 
> Architect
> 
> 
> Ph: 92.334.5100153
> 
> Skype ID: gabbasb
> 
> 
> www.enterprisedb.com
> 
> Follow us on Twitter
> @EnterpriseDB 
> 
> Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> 

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Abbas B. <abb...@en...>]

On Wed, Jun 3, 2015 at 8:57 PM, Manikandan Soundarapandian <ma...@vt...>
wrote:

>  Hi, Please see below
>
> \d+ Table1
> Table "Table1"
>     Column    |         Type          | Modifiers | Storage  | Stats
> target | Description
>
> --------------+-----------------------+-----------+----------+--------------+-------------
>  id          | numeric               |           | main     | 1000
> |
>  age          | numeric               |           | main     |
> 1000         |
>  gender       | numeric               |           | main     |
> 1000         |
>  col1     | character varying(5)  |           | extended | 1000         |
> Indexes:
>     "index_age_table1" btree (age)
>     "index_col1_table1" btree (col1)
>     "index_gender_table1" btree (gender)
>     "index_id_table1" btree (id)
> Has OIDs: no
> Distribute By: HASH(id)
> Location Nodes: ALL DATANODES
>
> \d+ Table2
> Table "Table2"
>      Column      |  Type   | Modifiers | Storage | Stats target |
> Description
>
> -----------------+---------+-----------+---------+--------------+-------------
>  id             | numeric |           | main    | 1000         |
>  rep             | numeric |           | main    | 1000         |
>  time    | numeric |           | main    | 1000         |
>
> Indexes:
>     "index_time_table2" btree (time)
>     "index_id_table2" btree (id)
>     "index_rep_table2" btree (rep)
> Has OIDs: no
> Distribute By: HASH(id)
> Location Nodes: ALL DATANODES
>
> Explain verbose of the query:
> GroupAggregate  (cost=0.07..0.10 rows=1 width=56)
>    Output: d.col1, round(((count(d.id) / 10))::double precision)
>    ->  Sort  (cost=0.07..0.08 rows=1 width=56)
>          Output: d.col1, d.id
>          Sort Key: d.col1
>          ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)
>                Output: d.col1, d.id
>                Hash Cond: (d.id = i.id)
>                ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=56)
>                      Output: d.col1, d.id
>                      Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1
>                      Remote query: SELECT col1, id FROM ONLY Table1 d
> WHERE true
>                ->  Hash  (cost=0.00..0.00 rows=1000 width=32)
>                      Output: i.id
>                      ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=32)
>                            Output: i.id
>                            Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1
>                            Remote query: SELECT id, time FROM ONLY Table2
> i WHERE true
>                            Coordinator quals: (((i.time)::double precision
> <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon
> YYYY'::text)))::double precis
> ion)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date
> - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))
>
> Also, one weird thing that I noticed is if I don't give the 'trunc()' date
> manipulation in the query and just gave some valid numbers in the between
> clause like 'between 5266 and 5632', am getting a different query plan
> and the query executes in just a few seconds.
>

The reason is that in this case the join gets pushed down to datanodes,
hence the major computation is done in parallel and only qualifying results
are transferred to the coordinator. Like Ashutosh has suggested you can
look to revise the planning of mutable functions in this case to get better
performance.
Another option is to rewrite the query in the following manner

Get current date in a variable curDate
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) and
save in startDate variable
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')) and save the
result in endDate
then re-write your query using the already computed values.

You can use libpq based C program to do this.


> The following is the query planner output when I give the numbers directly
> in the between clause.
> GroupAggregate  (cost=49.83..57.35 rows=1 width=56)
>    Output: d.col1, round(((count((count(d.id))) / 10))::double precision)
>    ->  Sort  (cost=49.83..52.33 rows=1000 width=56)
>          Output: d.col1, (count(d.id))
>          Sort Key: d.col1
>          ->  Data Node Scan on "__REMOTE_GROUP_QUERY__"  (cost=0.00..0.00
> rows=1000 width=56)
>                Output: d.col1, (count(d.id))
>                Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1,
> datanode_c5_d1
>                Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT
> d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT i.id
> FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <=
> 5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1 ORDER
> BY 1 DESC
>
> But my requirement is with the trunc() date manipulation.
>
> Thanks
>
>
> On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:
>
>
>
>  On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...>
> wrote:
>
>  Hi Abbas,
>
> I just looked at the planner for the query that am running. This is the
> query that am running,
>
> *SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2
> i WHERE d.id <http://d.id>=i.id <http://i.id> AND i.time BETWEEN
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND
> TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1
> ORDER BY d.col1 DESC;*
>
> And the query plan for a configuration with 4 datanodes and 1 coordinator,
>
> *GroupAggregate  (cost=0.07..0.10 rows=1 width=56)*
> *   ->  Sort  (cost=0.07..0.08 rows=1 width=56)*
> *         Sort Key: d.col1*
> *         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)*
> *               Hash Cond: (d.id <http://d.id> = i.id <http://i.id>)*
> *               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=56)*
> *                     Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> *               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)*
> *                     ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=32)*
> *                           Node/s: datanode_c2_d1, datanode_c3_d1,
> datanode_c4_d1, datanode_c5_d1*
> *                           Coordinator quals: (((i.time)::double
> precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text,
> 'DD Mon YYYY'::text)))::double precisio*
> *n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date -
> to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double
> precision)))*
>
>
> Table1 contains 22 million records and Table2 contains 10 million records.
> This query took several minutes (almost 35) to get results and one third
> of that time is spent in FetchTuple. Do you see something wrong with the
> query plan?
>
>
>
>  Can you provide output of
>
>  \d+ Table1
> \d+ Table2
>
>  I am interested in the distribution column of the tables.
>
>
>  Also please provide the output of EXPLAIN VERBOSE on the query.
>
>
>
>
> Since the multiplexed IO isn't the real bottleneck here, my proposal would
> be to change the way how populating the TupleStore at the coordinator is
> working and introduce parallelism there. Is this a right direction? Please
> let me know your thoughts.
>
> Thanks
> Mani
>
>
>
> On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote:
>
>
>
> On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...>
> wrote:
>
> Hi Abbas,
>
> Thanks for the reply. I understand how pgxc_node_receive is working to
> get incoming data from the datanodes.
> Yes, I have made experiments to study the postgres-xc system in a setup
> with 12 datanodes and a coordinator and performed join, groupby and
> order by operations on two tables with around 20 million and 10 million
> records respectively. I have found that around 20-30% of the time is
> spent in the 'FetchTuple' method which reads into the provided tupleslot
> one datarow at a time from the combiner's buffer which is in turn filled
> by the pgxc_node_receive method.
>
>
> XC planner pushes parts of query processing to datanodes to speed things
> up.
>
> In the queries you tried, did the query planner perform this optimization?
>
> If not then you can gain advantage by improving the planner to generate
> optimized plans that avoid UN-necessary materialization at the coordinator.
>
> This will bring the FetchTuple time down since there will be less tuples
> to fetch.
>
>
> If the planner is already producing optimized plans, then what's your
> proposal?
>
>
>
>
> Thanks
>
> On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:
> >
> >
> > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian
> > <ma...@vt...> wrote:
> >         Hi,
> >
> >
> >         I am a graduate student working on my research in parallel
> >         databases. I would like to know how the postgres-xc
> >         coordinator works. I understand that the datanodes run the
> >         query in parallel and the results are collected by the
> >         coordinator which runs any more computation that is required
> >         or just provides the output to the client that requested the
> >         query. I would like to know whether the coordinator does this
> >         data collection from datanodes in a sequential fashion?
> >
> >
> > The coordinator uses multiplexed IO using select on all fds of
> > datanodes. For more details please see pgxc_node_receive function in
> > pgxcnode.c. The loop for reading data on all set fds is sequential,
> > but the coordinator does not wait for data from the datanode to which
> > the coordinator had sent the query first.
> >
> >         For example, lets consider we want to run the query on table
> >         table_x which is hash distributed among 10 datanodes,
> >         select count(*) from table_x;
> >         Each datanode will run the query and give their local counts
> >         and the coordinator has to collect the individual counts and
> >         come up with the final count before sending the output. Is the
> >         data collection process at the coordinator done in a
> >         sequential fashion? I am actually looking to introduce some
> >         kind of parallelism in this data collection if it is
> >         sequential and do performance studies. Please clarify.
> >
> >
> > To improve performance of any system, first study the bottleneck, and
> > target to widen that. Have you done any study of Postgres-XC to find
> > where the performance bottleneck is?
> >
> >
> >
> >         --
> >         Thanks
> >         Mani
> >         Department of Computer Science
> >         Virginia Tech
> >
> >
>  ------------------------------------------------------------------------------
> >
> >         _______________________________________________
> >         Postgres-xc-developers mailing list
> >         Pos...@li...
> >
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
> >
> >
> >
> >
> >
> > --
> > --
> > Abbas
> > Architect
> >
> >
> > Ph: 92.334.5100153
> >
> > Skype ID: gabbasb
> >
> > www.enterprisedb.com
> >
> > Follow us on Twitter
> > @EnterpriseDB
> >
> > Visit EnterpriseDB for tutorials, webinars, whitepapers and more
> >
>
>
>
>
>
>
> --
> --
> *Abbas*
> Architect
>
>
> Ph: 92.334.5100153
>
> Skype ID: gabbasb
>
> www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
>
>
>
>
> --
>
>  --
> *Abbas*
>
>  Architect
>
>
> Ph: 92.334.5100153
>
>  Skype ID: gabbasb
>
>  www.enterprisedb.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [Postgres-xc-developers] Postgres-xc coordinator operation

[Manikandan S. <ma...@vt...>]

Thanks Abbas and Ashutosh! I'll look into the options that you provided to improve the performance here. 

Thanks
Mani




On Thu, Jun 4, 2015 at 1:53 AM -0700, "Abbas Butt" <abb...@en...> wrote:












On Wed, Jun 3, 2015 at 8:57 PM, Manikandan Soundarapandian <ma...@vt...> wrote:



  
  


Hi, Please see below



\d+ Table1

Table "Table1"

    Column    |         Type          | Modifiers | Storage  | Stats target | Description 

--------------+-----------------------+-----------+----------+--------------+-------------

 id          | numeric               |           | main     | 1000         | 

 age          | numeric               |           | main     | 1000         | 

 gender       | numeric               |           | main     | 1000         | 

 col1     | character varying(5)  |           | extended | 1000         | 

Indexes:

    "index_age_table1" btree (age)

    "index_col1_table1" btree (col1)

    "index_gender_table1" btree (gender)

    "index_id_table1" btree (id)

Has OIDs: no

Distribute By: HASH(id)

Location Nodes: ALL DATANODES



\d+ Table2

Table "Table2"

     Column      |  Type   | Modifiers | Storage | Stats target | Description 

-----------------+---------+-----------+---------+--------------+-------------

 id             | numeric |           | main    | 1000         | 

 rep             | numeric |           | main    | 1000         | 

 time    | numeric |           | main    | 1000         | 

 

Indexes:

    "index_time_table2" btree (time)

    "index_id_table2" btree (id)

    "index_rep_table2" btree (rep)

Has OIDs: no

Distribute By: HASH(id)

Location Nodes: ALL DATANODES



Explain verbose of the query:

GroupAggregate  (cost=0.07..0.10 rows=1 width=56)

   Output: d.col1, round(((count(d.id) / 10))::double precision)

   ->  Sort  (cost=0.07..0.08 rows=1 width=56)

         Output: d.col1, d.id

         Sort Key: d.col1

         ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)

               Output: d.col1, d.id

               Hash Cond: (d.id = i.id)

               ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=56)

                     Output: d.col1, d.id

                     Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1

                     Remote query: SELECT col1, id FROM ONLY Table1 d WHERE true

               ->  Hash  (cost=0.00..0.00 rows=1000 width=32)

                     Output: i.id

                     ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=32)

                           Output: i.id

                           Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1

                           Remote query: SELECT id, time FROM ONLY Table2 i WHERE true

                           Coordinator quals: (((i.time)::double precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double precis

ion)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double precision)))



Also, one weird thing that I noticed is if I don't give the 'trunc()' date manipulation in the query and just gave some valid numbers in the between clause like 'between 5266 and 5632', am getting a different query plan and the query executes in just a few seconds.
The reason is that in this case the join gets pushed down to datanodes, hence the major computation is done in parallel and only qualifying results are transferred to the coordinator. Like Ashutosh has suggested you can look to revise the planning of mutable functions in this case to get better performance.
Another option is to rewrite the query in the following manner

Get current date in a variable curDate
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) and save in startDate variable
Compute TRUNC(curDate - TO_DATE('01 Jan 2000','DD Mon YYYY')) and save the result in endDate
then re-write your query using the already computed values.

You can use libpq based C program to do this.
 
 The following is the query planner output when I give the numbers directly in the between clause. 

GroupAggregate  (cost=49.83..57.35 rows=1 width=56)

   Output: d.col1, round(((count((count(d.id))) / 10))::double precision)

   ->  Sort  (cost=49.83..52.33 rows=1000 width=56)

         Output: d.col1, (count(d.id))

         Sort Key: d.col1

         ->  Data Node Scan on "__REMOTE_GROUP_QUERY__"  (cost=0.00..0.00 rows=1000 width=56)

               Output: d.col1, (count(d.id))

               Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1

               Remote query: SELECT l.a_1, count(l.a_2) FROM ((SELECT d.col1, d.id FROM ONLY Table1 d WHERE true) l(a_1, a_2) JOIN (SELECT i.id FROM ONLY Table2 i WHERE ((i.time >= 5266::numeric) AND (i.time <= 5632::numeric))) r(a_1) ON (true)) WHERE (l.a_2 = r.a_1) GROUP BY 1 ORDER BY 1 DESC



But my requirement is with the trunc() date manipulation.



Thanks



On Wed, 2015-06-03 at 18:08 -0700, Abbas Butt wrote:

    



    



    On Wed, Jun 3, 2015 at 5:10 PM, Manikandan Soundarapandian <ma...@vt...> wrote:


    
        Hi Abbas,

        

        I just looked at the planner for the query that am running. This is the query that am running,

        

        SELECT d.col1 RVALUE, ROUND(COUNT(d.col2)/10) COUNT FROM Table1 d, Table2 i WHERE d.id=i.id AND i.time BETWEEN TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')+1-365) AND TRUNC(CURRENT_DATE-TO_DATE('01 Jan 2000','DD Mon YYYY')) GROUP BY d.col1 ORDER BY d.col1 DESC;

        

        And the query plan for a configuration with 4 datanodes and 1 coordinator,

        

        GroupAggregate  (cost=0.07..0.10 rows=1 width=56)

           ->  Sort  (cost=0.07..0.08 rows=1 width=56)

                 Sort Key: d.col1

                 ->  Hash Join  (cost=0.01..0.06 rows=1 width=56)

                       Hash Cond: (d.id = i.id)

                       ->  Data Node Scan on Table1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=56)

                             Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1

                       ->  Hash  (cost=0.00..0.00 rows=1000 width=32)

                             ->  Data Node Scan on Table2 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=32)

                                   Node/s: datanode_c2_d1, datanode_c3_d1, datanode_c4_d1, datanode_c5_d1

                                   Coordinator quals: (((i.time)::double precision <= trunc(((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)))::double precisio

        n)) AND ((i.time)::double precision >= trunc(((((('now'::cstring)::date - to_date('01 Jan 2000'::text, 'DD Mon YYYY'::text)) + 1) - 365))::double precision)))

        

        

        Table1 contains 22 million records and Table2 contains 10 million records.

        This query took several minutes (almost 35) to get results and one third of that time is spent in FetchTuple. Do you see something wrong with the query plan?

        

    


    

    



    Can you provide output of 

    



    \d+ Table1

    \d+ Table2

    



    I am interested in the distribution column of the tables. 

    

    



    Also please provide the output of EXPLAIN VERBOSE on the query.

    

    



    



    
        

        Since the multiplexed IO isn't the real bottleneck here, my proposal would be to change the way how populating the TupleStore at the coordinator is working and introduce parallelism there. Is this a right direction? Please let me know your thoughts.

        

        Thanks

        Mani
    


    
        

        

        On Wed, 2015-06-03 at 01:49 -0700, Abbas Butt wrote: 

        
            

            

            On Tue, Jun 2, 2015 at 10:17 PM, Manikandan Soundarapandian <ma...@vt...> wrote:

            
                Hi Abbas,

                

                Thanks for the reply. I understand how pgxc_node_receive is working to

                get incoming data from the datanodes.

                Yes, I have made experiments to study the postgres-xc system in a setup

                with 12 datanodes and a coordinator and performed join, groupby and

                order by operations on two tables with around 20 million and 10 million

                records respectively. I have found that around 20-30% of the time is

                spent in the 'FetchTuple' method which reads into the provided tupleslot

                one datarow at a time from the combiner's buffer which is in turn filled

                by the pgxc_node_receive method. 

            
            

            XC planner pushes parts of query processing to datanodes to speed things up.

            

            In the queries you tried, did the query planner perform this optimization?

            

            If not then you can gain advantage by improving the planner to generate optimized plans that avoid UN-necessary materialization at the coordinator.

            

            This will bring the FetchTuple time down since there will be less tuples to fetch.

            

            

            If the planner is already producing optimized plans, then what's your proposal?

            

            

            

            
                

                Thanks 

                

                On Wed, 2015-06-03 at 09:45 +0500, Abbas Butt wrote:

                >

                >

                > On Wed, Jun 3, 2015 at 4:11 AM, Manikandan Soundarapandian

                > <ma...@vt...> wrote:

                >         Hi,

                >

                >

                >         I am a graduate student working on my research in parallel

                >         databases. I would like to know how the postgres-xc

                >         coordinator works. I understand that the datanodes run the

                >         query in parallel and the results are collected by the

                >         coordinator which runs any more computation that is required

                >         or just provides the output to the client that requested the

                >         query. I would like to know whether the coordinator does this

                >         data collection from datanodes in a sequential fashion?

                >

                >

                > The coordinator uses multiplexed IO using select on all fds of

                > datanodes. For more details please see pgxc_node_receive function in

                > pgxcnode.c. The loop for reading data on all set fds is sequential,

                > but the coordinator does not wait for data from the datanode to which

                > the coordinator had sent the query first.

                >

                >         For example, lets consider we want to run the query on table

                >         table_x which is hash distributed among 10 datanodes,

                >         select count(*) from table_x;

                >         Each datanode will run the query and give their local counts

                >         and the coordinator has to collect the individual counts and

                >         come up with the final count before sending the output. Is the

                >         data collection process at the coordinator done in a

                >         sequential fashion? I am actually looking to introduce some

                >         kind of parallelism in this data collection if it is

                >         sequential and do performance studies. Please clarify.

                >

                >

                > To improve performance of any system, first study the bottleneck, and

                > target to widen that. Have you done any study of Postgres-XC to find

                > where the performance bottleneck is?

                >

                >

                >

                >         --

                >         Thanks

                >         Mani

                >         Department of Computer Science

                >         Virginia Tech

                >

                >         ------------------------------------------------------------------------------

                >

                >         _______________________________________________

                >         Postgres-xc-developers mailing list

                >         Pos...@li...

                >         https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers

                >

                >

                >

                >

                >

                > --

                > --

                > Abbas

                > Architect

                >

                >

                > Ph: 92.334.5100153

                >

                > Skype ID: gabbasb

                >

                > www.enterprisedb.com

                >

                > Follow us on Twitter

                > @EnterpriseDB

                >

                > Visit EnterpriseDB for tutorials, webinars, whitepapers and more

                >

                

                

                

            
            

            

            

            -- 

            -- 

            Abbas

            Architect

            

            

            Ph: 92.334.5100153

            

            Skype ID: gabbasb

            

            www.enterprisedb.com

            

            Follow us on Twitter

            @EnterpriseDB 

            

            Visit EnterpriseDB for tutorials, webinars, whitepapers and more

            

        
        

        

    


    

    

    

    -- 


    -- 

    Abbas


    Architect

    



    

    Ph: 92.334.5100153

    



    Skype ID: gabbasb

    



    www.enterprisedb.com

    

    Follow us on Twitter

    @EnterpriseDB 

    

    Visit EnterpriseDB for tutorials, webinars, whitepapers and more

    









-- 
-- 
Abbas



Architect

Ph: 92.334.5100153





Skype ID: gabbasb
www.enterprisedb.com




Follow us on Twitter
@EnterpriseDB 

Visit EnterpriseDB for tutorials, webinars, whitepapers and more