[Postgres-xl-general] Different query plans on different coordinators in same cluster

Dennis dennisr at visi.com
Fri Aug 22 08:08:48 PDT 2014


One piece of information that might be relevant, below is the output from pgxc_node on both coordinators.  The datanodes have different nodeis_preferred value configurations.

coordinator1(coord_prges01 - fast plan):

select * from pg_catalog.pgxc_node ;
    node_name     | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id   
------------------+-----------+-----------+-----------+----------------+------------------+------------
 coord_pgres01    | C         |      5432 | pgres01   | f              | f                | -888522620
 coord_pgres02    | C         |      5432 | pgres02   | f              | f                |  -47372573
 datanode_pgres01 | D         |     20004 | pgres01   | t              | t                | 1856569337
 datanode_pgres02 | D         |     20004 | pgres02   | f              | f                |  126081261



coordinator2 (coord_pgres02 - slow plan):

select * from pg_catalog.pgxc_node;
    node_name     | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id   
------------------+-----------+-----------+-----------+----------------+------------------+------------
 coord_pgres01    | C         |      5432 | pgres01   | f              | f                | -888522620
 coord_pgres02    | C         |      5432 | pgres02   | f              | f                |  -47372573
 datanode_pgres01 | D         |     20004 | pgres01   | t              | f                | 1856569337
 datanode_pgres02 | D         |     20004 | pgres02   | f              | t                |  126081261
(4 rows)

On Aug 21, 2014, at 11:21 PM, Dennis <dennisr at visi.com> wrote:

> I have a postgres-xl cluster with two datanodes, two coordinators and a gtm node.  coordinator1 and datanode1 are together on one box, and coordinator2 and datanode2 are running together on a different box.   I am using postgres-xl from github - 8/6/2014 release.
> 
> Coordinator1 is producing a faster query plan. The only difference that I can think of at the moment that is related to this issue is that I have run analyze verbose on coordinator2 but not on coordinator1.  Coordinator1, was using the same query plan that coordinator1 is producing before I ran analyze verbose on coordinator2.  After running analyze on coordinator2 I am getting a slower query plan (good plan is doing index only scans while bad plan is now using bitmap index scans after analyze.)  How can this happen, is this a bug? Is there a way to have coordinator2 follow the same query plan as coordinator1?
> 
> Query:
> SELECT
>   sum(t_quad_f.dollars) as dollars,
>   nb_products.category
> FROM
>   sn_dm_b.nb_periods,
>   sn_dm_b.nb_products,
>   sn_dm_b.t_quad_f
> WHERE
>   t_quad_f.upc = nb_products.product_key AND
>   t_quad_f.period = nb_periods.period AND
>   t_quad_f.store_tag  ='4340' AND
>   nb_periods.orderno between '356' and '368'
>   group by category;
> 
> Indexes on t_quad_f:
>     "qd_f_p_idx" btree (period)
>     "qd_f_s_idx" btree (store_tag)
>     "qd_f_u_idx" btree (upc)
> 
> Indexes on nb_products:
>     "pk_nb_products" PRIMARY KEY, btree (product_key)
>     "prd_pl_idx" btree (product_key)
> 
> Indexes on nb_period:
>     "pk_nb_periods" PRIMARY KEY, btree (period)
>     "per_lvl_idx" btree (lvl)
> 
> 
> Coordinator1 (faster plan):
> 
> HashAggregate  (cost=24.84..24.85 rows=1 width=50)
>    ->  Remote Subquery Scan on all (datanode_pgres01,datanode_pgres02)  (cost=0.00..24.83 rows=1 width=50)
>          ->  HashAggregate  (cost=0.00..24.83 rows=1 width=50)
>                ->  Nested Loop  (cost=0.00..24.83 rows=1 width=50)
>                      ->  Nested Loop  (cost=0.00..16.55 rows=1 width=50)
>                            ->  Index Scan using qd_f_s_idx on t_quad_f  (cost=0.00..8.27 rows=1 width=54)
>                                  Index Cond: (store_tag = 4340)
>                            ->  Index Scan using pk_nb_periods on nb_periods  (cost=0.00..8.28 rows=1 width=4)
>                                  Index Cond: (period = t_quad_f.period)
>                                  Filter: ((orderno >= 356) AND (orderno <= 368))
>                      ->  Index Scan using pk_nb_products on nb_products  (cost=0.00..8.27 rows=1 width=64)
>                            Index Cond: ((product_key)::text = (t_quad_f.upc)::text)
> 
> Coordinator2 (slow plan):
> 
> HashAggregate  (cost=3521640.69..3521641.60 rows=91 width=29)
>    ->  Remote Subquery Scan on all (datanode_pgres01,datanode_pgres02)  (cost=320939.23..3521393.44 rows=49449 width=29)
>          ->  HashAggregate  (cost=320939.23..3521393.44 rows=91 width=29)
>                ->  Hash Join  (cost=320939.23..3521393.44 rows=49449 width=29)
>                      Hash Cond: ((t_quad_f.upc)::text = (nb_products.product_key)::text)
>                      ->  Nested Loop  (cost=214918.65..3414260.26 rows=49449 width=21)
>                            ->  Seq Scan on nb_periods  (cost=0.00..8.66 rows=11 width=4)
>                                  Filter: ((orderno >= 356) AND (orderno <= 368))
>                            ->  Bitmap Heap Scan on t_quad_f  (cost=214918.65..310141.24 rows=24527 width=25)
>                                  Recheck Cond: ((store_tag = 4340) AND (period = nb_periods.period))
>                                  ->  BitmapAnd  (cost=214918.65..214918.65 rows=24527 width=0)
>                                        ->  Bitmap Index Scan on qd_f_s_idx  (cost=0.00..25885.54 rows=1398053 width=0)
>                                              Index Cond: (store_tag = 4340)
>                                        ->  Bitmap Index Scan on qd_f_p_idx  (cost=0.00..188677.22 rows=10219684 width=0)
>                                              Index Cond: (period = nb_periods.period)
>                      ->  Hash  (cost=99207.48..99207.48 rows=545048 width=38)
>                            ->  Seq Scan on nb_products  (cost=0.00..99207.48 rows=545048 width=38)
> ------------------------------------------------------------------------------
> Slashdot TV.  
> Video for Nerds.  Stuff that matters.
> http://tv.slashdot.org/_______________________________________________
> Postgres-xl-general mailing list
> Postgres-xl-general at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-general

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20140822/31a450e4/attachment.htm>


More information about the postgres-xl-general mailing list