We've got a time-series network data which we store in a Vertica table. The UI needs to show data in descending order of the timestamp. I tried passing the query to the database designer but it doesn't suggest any projection for the descending order, it already has the projection which order by timestamp in ascending order. I also tried creating projection with the timestamp order by descending but Vertica throws and error - "Projections can only be sorted in ascending order". Since UI needs to show events in descending order of timestamp the SORT cost of the query is very high - can we optimize it in any way?
The following query is very slow (SORT takes a lot of time even I supply an event_timestamp filter to consider only 1 day worth of events) select * from public.f_network_events order by event_timestamp desc limit 1000;
You can't ORDER BY ts DESCENDING a projection in Vertica, I'm afraid.
The trick I use for this necessity is to add a column:
tssort INTEGER DEFAULT TIMESTAMPDIFF(microsecond,ts,'2100-01-01::TIMESTAMP)
.. to sort the projection by that, to calculate that TIMESTAMPDIFF() in the query and use it for the WHERE condition.
Not of breathtaking beauty, I agree, but worth the trouble in Big Data scenarios ...