Slow query when using ORDER BY in Postgres

Question

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?

• user1822: Unrelated, but: natural join is a really bad habit to use. You should use an explicit join (it won't make it faster, but it will make it less error prone)
• Evan Carroll: Try adding OFFSET 0 to your inner sub select.
• cal: @EvanCarroll Hmm didn't see to help, thanks though
• cal: @a_horse_with_no_name Hmmm, didn't think about the drawbacks (column names changing, new columsn), thanks for letting me know

Answer 1

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.