order by desc performences best practic

eli_revach

Projection sort order do not support order by desc , my use case is simple data out query, no aggrigation but with order by desc on first column of the projection , we have vertica in one host setup , 100m records table the query do not have amy joins .its a simple flat query that scan last week data rang do some distinc on inline query and bring top 2k records in desc order , when i remove the desc from my order by the query is very very fast (9sec) however using desc the query run about 120sec, the execution plan of the none desc test shoe no order , where the one with the desc order show sort stage , what are the options her ? this is 8 core node and we target it to small customers ,we still need the abiliys to provid desc order result on top of a time range on 100m table with resonable timing. Asc order work very goodddddd

Bing_Wu

I am wondering what if you add an invert column to the underlying table and sort on that instead? It is a hack but may work. 

On a side note, I thought 9 seconds on a 100mm row table scan is kind of slow for Vertica. Did you say the whole table is 100mm or that is how much for one week? Not sure if it is related to your problem with sort order but it might.

Daniel_Leybovic

Hi!

I don't know about native solution, but you always can add a column with defaults that swaps order.
For example numeric types its just add minus. For date/time/interval column you can add a default column that extracts epoch, varchars depends on logic.

daniel=> create table tbl (id int, name char(3));
CREATE TABLE
daniel=> alter table tbl add column id_desc int default -id;
ALTER TABLE
daniel=> create projection tbl_prj_descid as select * from tbl order by id_desc;
CREATE PROJECTION
daniel=> copy tbl(id, name) from stdin direct;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|foo
>> 2|bar
>> 3|egg
>> \.
daniel=> select * from tbl;
 id | name | id_desc
----+------+---------
  3 | egg  |      -3
  2 | bar  |      -2
  1 | foo  |      -1
(3 rows)

I hate you GetSatisfaction! More than 6 years Im familiar with this system, except external look - a face, it didn't improved anything: no features added (a basic one - private messaging, how many time it was asked from GS), no normal RSS.
And why it redirects to GetSetisfaction site when profile is seeing? Advertisement? But why? This system is not for free. I spend a money and them put their adv?

eli_revach

Yes , i also think about that , i  more  interested on native solution .

Thanks anyway