[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