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

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

On Mar 3, 2014, at 8:45 PM, David E. Wheeler <da...@ju...> 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:

Sleeping on it, I realized that it might be thinking that the date math is volatile. So I tried a query using constant values, instead:

=# 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 >= '2014-01-15'::timestamp
-#    AND  tr.txn_timestamp_utc < '2014-01-16'::timestamp
-# ;
                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: tr.subscriber_id, s.subscriber, tr.txn_id
   Node/s: rptdata01, rptdata02, rptdata03, rptdata04
   Remote query: SELECT tr.subscriber_id, s.subscriber AS subscriber_name, tr.txn_id AS tracking_number FROM (subscriber_482900.transactions tr LEFT JOIN config.subscribers s ON ((tr.subscriber_id = s.subscriber_id))) WHERE (((tr.subscriber_id = 482900) AND (tr.txn_timestamp_utc >= '2014-01-15 00:00:00'::timestamp without time zone)) AND (tr.txn_timestamp_utc < '2014-01-16 00:00:00'::timestamp without time zone))
(4 rows)

That’s *so* much better. Makes me realize we probably have the same problem in the original Postgres version of this query.

Thanks,

David