Issue with ORDER BY on a big sized projection
We have a 3 node Vertica 7.x cluster in our production database. We have a table/projection (right now there is just one super projection) which has roughly 400 million rows in it. I am running a query which does an ORDER BY sort on the table but the database does not return any data and gets stuck there in processing.
As expected, we are coming up with an improved query specific projection suggested by Vertica Management Console which by the way has an ORDER BY clause suggested as a part of a new projection. But before deploying it on production and see if that helps run the query or not, I was confused even if the new projection's CREATE statement will work? Given the fact that, the table on which we are creating this new projection doesn't return any data for a SELECT statement having an ORDER BY in it. Also, even the EXPLAIN on the query doesn't return anything. I broke down the query into pieces and it gets stuck during the execution of ORDER BY.
I don't wish to leave the cluster in an inconsistent state or something. It would be very helpful if anyone can throw some light on both issues I have mentioned above (about slow unresponsive ORDERBY on big table and things happening under the hood while deploying new projection with an ORDERBY clause).
My query is like: