How Can I Write This Postgres Query In Amazon Redshift Such That It Is As Optimized As It Was In Postgres?

Here is my original query that I was using in postgres - SELECT a.id, (SELECT val FROM database.detail x WHERE name = 'blablah' AND x.id = b.id) AS myGroup

Solution 1:

Redshift Query optimization comes from Cluster, Table Design, DataLoading, Data Vacuuming &Analyzing over the table.

Let me answer some core touch points in the above list. 1. Make Sure your table mytable, detail, client has proper SORT_KEY, DIST_KEY 2. Make Sure all your tables in join are analzed and vaccumed properly.

Here is another version of your same SQL written in Redshift format.

Few Tweaks I made are

1. Used "With Clause" to Optimized Cluster level computation
2. Used Joins the proper way and make sure left/right join matters based on data.
3. Used date_range with clause table for kind of object orientation.
4. Used Group By in the main SQL below.

My Version of Redshift SQL

/** Date Range Computation **/with date_range as (
    select ( current_Date-interval'2 weeks' ) as two_weeks
),
/** Filter main ResultSet**/
myGroupSet as (
    SELECT b.val AS myGroup,
           c.username,
           a.someCode,
           a.timeTaken,
           (casewhen (b.name =='name1') THEN b.val::INTEGERELSE0END ) as name11,
           (casewhen (b.name =='name2') THEN b.val::INTEGERELSE0END ) as name12
      FROM database.myTable a,
      join date_range dr on a.date > dr.two_weeks
      join database.detail b on b.id = a.id
      join database.client c on c.c_id = a.c_id
     where a.date >current_Date-interval'2 weeks'
)
/** Apply Aggregation **/select myGroup, username, someCode, timeTaken, date,
       sum(name1), sum(name2)
  from myGroupSet
  groupby myGroup, username, someCode, timeTaken, date