CODEWITHSUNDEEP
databasedatabase-designbigdatadatabase-performancevertica
Rajat Paliwal
Rajat Paliwal

Reputation: 702

Vertica - projections for order by descending order

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;

Upvotes: 0

Views: 387

Answers (1)

marcothesane
marcothesane

Reputation: 6741

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 ...

Upvotes: 3

Related Questions