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?

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) — user1822
– user1822, Commented Jun 21, 2018 at 16:51
@a_horse_with_no_name Hmmm, didn't think about the drawbacks (column names changing, new columsn), thanks for letting me know — cal
– cal, Commented Jun 21, 2018 at 18:12

user1822 · Accepted Answer · 2018-06-21 17:01:22Z

2

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.

answered Jun 21, 2018 at 17:01

user1822

Add a comment |

Stack Exchange Network

Slow query when using ORDER BY in Postgres

1 Answer 1

Hot Network Questions

Slow query when using ORDER BY in Postgres

1 Answer 1

Related

Hot Network Questions