Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

order by on JOIN of tables is very slow

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?

Answers

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

  • @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_KnicelyJim_Knicely Administrator
    edited January 2020

    @rajatpaliwal86 - Is athena.f_virtual_fields a FLEX table?

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

    Yes, f_virtual_fields is a FLEX table.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.