CODEWITHSUNDEEP
database-performanceamazon-redshift
Michael Ismert
Michael Ismert

Reputation: 31

Redshift select * vs select single column

I'm having the following Redshift performance issue:

I have a table with ~ 2 billion rows, which has ~100 varchar columns and one int8 column (intCol). The table is relatively sparse, although there are columns which have values in each row.

The following query:

select colA from tableA where intCol = ‘111111’;

returns approximately 30 rows and runs relatively quickly (~2 mins)

However, the query:

select * from tableA where intCol = ‘111111’;

takes an undetermined amount of time (gave up after 60 mins).

I know pruning the columns in the projection is usually better but this application needs the full row.

Questions:
Is this just a fundamentally bad thing to do in Redshift? If not, why is this particular query taking so long? Is it related to the structure of the table somehow? Is there some Redshift knob to tweak to make it faster? I haven't yet messed with the distkey and sortkey on the table, but it's not clear that those should matter in this case.

Upvotes: 3

Views: 1883

Answers (1)

Masashi M
Masashi M

Reputation: 2757

The main reason why the first query is faster is because Redshift is a columnar database. A columnar database stores table data per column, writing a same column data into a same block on the storage. This behavior is different from a row-based database like MySQL or PostgreSQL. Based on this, since the first query selects only colA column, Redshift does not need to access other columns at all, while the second query accesses all columns causing a huge disk access.

To improve the performance of the second query, you may need to set "sortkey" to colA column. By setting sortkey to a column, that column data will be stored in sorted order on the storage. It reduces the cost of disk access when fetching records with a condition including that column.

Upvotes: 5

Related Questions