Re: [Postgres-xc-developers] connection to server was lost

[David E. W. <da...@ju...>]

On Mar 3, 2014, at 7:50 PM, Ashutosh Bapat <ash...@en...> wrote:

> Although, it looks like the quals are shippable, there may be something which is being deemed as volatile (function or operator or a cast) or some parameter. Unfortunately, I don't have time to dig through it. Can you please check what happens to the JOIN if you say removing WHERE clause. With that the entire join should be shippable, assuming that the two relations are distributed on subscriber_id.

config.subscribers is replicated to all nodes, and subscriber_482900.transactions is a partition, and the only subscriber_id in the whole table is 482900. It's distributed on another column, account_code. Taking out the WHERE clause is by definition a table scan:

=# EXPLAIN VERBOSE
-# SELECT tr.subscriber_id  AS subscriber_id
-#      , s.subscriber      AS subscriber_name
-#      , tr.txn_id         AS tracking_number
-#   FROM subscriber_:SUB_CODE.transactions tr
-#   LEFT JOIN config.subscribers s ON tr.subscriber_id = s.subscriber_id
-#  WHERE tr.subscriber_id = :SUB_CODE
-#    AND  tr.txn_timestamp_utc >= :'RPT_DAY'::timestamp - (:DAYS_BACK || ' days')::interval + (:CUTOFF_HOUR || ' hours')::interval
-#    AND  tr.txn_timestamp_utc< :'RPT_DAY'::timestamp  + (:CUTOFF_HOUR || ' hours')::interval
-# ;
                                                                                                                                            QUERY PLAN                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..0.01 rows=1 width=44)
   Output: tr.subscriber_id, s.subscriber, tr.txn_id
   Join Filter: (tr.subscriber_id = s.subscriber_id)
   ->  Data Node Scan on transactions "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=12)
         Output: tr.subscriber_id, tr.txn_id
         Node/s: rptdata01, rptdata02, rptdata03, rptdata04
         Remote query: SELECT subscriber_id, txn_id, txn_timestamp_utc FROM ONLY subscriber_482900.transactions tr WHERE (subscriber_id = 482900)
         Coordinator quals: ((tr.txn_timestamp_utc < ('2014-01-16 00:00:00'::timestamp without time zone + ('0 hours'::cstring)::interval)) AND (tr.txn_timestamp_utc >= (('2014-01-16 00:00:00'::timestamp without time zone - ('1 days'::cstring)::interval) + ('0 hours'::cstring)::interval)))
   ->  Data Node Scan on subscribers "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1000 width=36)
         Output: s.subscriber, s.subscriber_id
         Node/s: rptdata01
         Remote query: SELECT subscriber, subscriber_id FROM ONLY config.subscribers s WHERE (subscriber_id = 482900)

David