The following SQL queries might help you speed up your query results. 

This topic includes:

Use CREATE TABLE AS or INSERT INTO

SELECT * FROM my_table

Presto uses JSON text to materialize query results. If the preceding table contains 100GB of data, the coordinator transfers more than 100GB of JSON text to save the query result. So, even if the query computation is almost finished, outputting JSON results takes a long time.

Use CREATE TABLE (table) AS SELECT …

DROP TABLE IF EXISTS my_result;
CREATE TABLE my_result AS SELECT * FROM my_table;

You can parallelize the query result output process by using CREATE TABLE AS SELECT statement. To clean up the result table beforehand, add a DROP TABLE statement at the top of your query. The result output performance will be 5x ~ faster than running SELECT *. Our Presto skips the JSON output process and directly produces a 1-hour partitioned table.

Use INSERT INTO (table) SELECT …

You can also use INSERT INTO (table) AS SELECT … to append the data to an existing table. It can improve the query result output performance:

CREATE TABLE IF NOT EXISTS my_result(time bigint);
INSERT INTO my_result SELECT * FROM my_table;

If the subsequent SELECT statement does not produce the time column, INSERT INTO attaches the query execution time as time column values. You can find the inserted rows using the query execution time.

Avoid using SELECT *

SELECT * FROM my_table

Treasure Data uses a column-oriented storage format, so accessing a small set of columns is really fast, but as the number of columns increases in your query, it deteriorates the query performance. Be selective in choosing columns:

SELECT id, name, address FROM my_table

(Experimental) Use result_output_redirect

(This is an experimental feature that is subject to change.) By setting result_output_redirect='true' within a magic comment, you can make the query output faster:

-- set session result_output_redirect='true'
select * FROM my_table

With this query hint, our Presto produces the query results in parallel and can skip the JSON conversion process at the coordinator.

But if the ORDER BY clause or an upper case field name is used in the query, this magic comment is ignored.