order by on JOIN of tables is very slow

rajatpaliwal86

I've multiple tables that need to be JOINed for few specific queries, however, when I use order by on a field on the first table after JOIN query, the query runs extremely slow. How can we make such queries faster?

e.g.
The following query is fast
select athena.f_network_events.*, maptostring(athena.f_virtual_fields.raw) as eventjson from athena.f_network_events JOIN athena."f_virtual_fields" on athena.f_network_events.flow_id = athena."f_virtual_fields".flow_id AND athena.f_network_events.component_id = athena."f_virtual_fields".component_id AND athena.f_network_events.event_timestamp > to_timestamp_tz(1576795262) AND athena.f_network_events.event_timestamp < to_timestamp_tz(1576816862) limit 100;

The below query is extremely slow after adding order by in the end.
select athena.f_network_events.*, maptostring(athena.f_virtual_fields.raw) as eventjson from athena.f_network_events JOIN athena."f_virtual_fields" on athena.f_network_events.flow_id = athena."f_virtual_fields".flow_id AND athena.f_network_events.component_id = athena."f_virtual_fields".component_id AND athena.f_network_events.event_timestamp > to_timestamp_tz(1576795262) AND athena.f_network_events.event_timestamp < to_timestamp_tz(1576816862) order by athena.f_network_events.event_timestamp desc limit 100;

However, the order by on the single table( athena.f_network_events) is fast.
I also tried passing the query to database designer but it couldn't suggest projection for the JOIN and order by combination.
What can we do for making the order by fast on a JOIN query?

qinchaofeng

Is 'event_timestamp ' the 'order by' column，Please take a look at the execution plan，you can refer ORDER BY Queries

rajatpaliwal86

@qinchaofeng said:
Is 'event_timestamp ' the 'order by' column，Please take a look at the execution plan，you can refer ORDER BY Queries

Yes, event_timestamp is the order by column in the first table. There is no issue when the single table is referred. However, the issue is when a JOIN query is used, shall I also add event_timestamp in the second table and have projection order by event_timestamp for the second table as well in order to allow order by event_timestamp on a JOINed query?

Jim_Knicely

@rajatpaliwal86 - Is athena.f_virtual_fields a FLEX table?

rajatpaliwal86

@Jim_Knicely said:
@rajatpaliwal86 - Is athena.f_virtual_fields a FLEX table?

Yes, f_virtual_fields is a FLEX table.