Slow query when using ORDER BY in Postgres This query 

SELECT *
FROM
  ( SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids 
NATURAL INNER JOIN events;


Returns 456 results and takes about 20 ms.

Adding ORDER BY

SELECT *
FROM
  (SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids 
NATURAL INNER JOIN events
ORDER BY event_ts DESC;


causes the query to take a very long time to complete, about 10 minutes.

Any pointers on how to fix this?
Try putting the original (fast) query into a common table expression:

with my_events as (
  select *
  from (
    ...
  ) as ids
     join events on ids.event_id = events.id
)
select *
from my_events
order by event_ts desc;


CTEs are optimized independently from the rest of the query, so that should run the base query with the fast plan. Ordering the 456 rows should be fairly quick.