Quicksight always adds "order by" to sql query

Q&A
, ,
1

Hi all.
i have a “table” view on my dashboard.
when I use ‘direct query’ or even ‘custom sql’ quicksight always adds “order by column1, column 2 , …” to sql query.
I need to run sql without sorting due it affects the performance significantly.
how to disable this sorting? i still have no ideas .. please advise.

2

Hi @akurinov

Quick Sight adds an ORDER BY clause automatically to ensure predictable and consistent ordering of data, especially for pagination and reliable rendering in visualizations like tables. Currently, there’s no direct option to disable this automatic sorting, as it’s built into Quick Sight’s data-fetching mechanism to manage pagination effectively.

If you haven’t already done so, consider the following to mitigate performance impact:

  • Adding Indexes: Create indexes on the columns Quick Sight frequently includes in the automatic ORDER BY .
  • Redshift Sort Keys: If you’re using Amazon Redshift, define appropriate sort keys on these columns to significantly improve query performance.

These steps can help optimize query execution, even though Quick Sight’s automatic sorting cannot be disabled directly.

3

thank you for quick response. i use redshift and have sort keys there however i can see all columns in ‘order by’ clause. is there a way to limit columns that added to “order by”
i tried to add ‘order by’ into my custom sql but it didn’t help.

4

Hi @akurinov -

Look into this relatively new feature that helps to address this problem. If you have a column/field that can be used as an unique key, that can be used as a more performant option than the generated ORDER BY clause.