Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table recommendations (
  ticker text, 
  date_recommended date,
  current_recommendation int,
  comments text);
insert into recommendations values 
  ('T.11','yesterday',21,'comments21'),
  ('V.12','yesterday',22,'comments22'),
  ('U.13','today',23,'comments23'),
  ('W.14','today',24,'comments24')
  ;
create table scr_tickers (
  ticker text);
insert into scr_tickers values 
  ('U.13')
  ;
create table approved_recommendations (
  ticker text, 
  date_approved date,
  approved_recommendation int);
insert into approved_recommendations values 
  ('W.14','today',24)
  ;
create table recommendations_history (
  ticker text, 
  date_recommended date,
  recommendation_value int);
insert into recommendations_history values 
  ('W.14','today',24)
  ;

CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 1

prepare s0 as 
SELECT concat_ws(' ', z.ticker,
    COALESCE(TO_CHAR(a.date_approved,   'YYMMDD'),
             TO_CHAR(z.date_recommended,'YYMMDD'),
             '000000'),
     CASE WHEN s.ticker IS NOT NULL THEN 6
          ELSE COALESCE(a.approved_recommendation, current_recommendation)
     END,
     REPLACE(COALESCE(comments,'N/C'),' ','_'),
     h.recommendation_value)
FROM recommendations z
LEFT JOIN scr_tickers s              USING (ticker)
LEFT JOIN approved_recommendations a USING (ticker)
LEFT JOIN LATERAL (SELECT rh.ticker, rh.recommendation_value
        FROM recommendations_history rh
        WHERE z.current_recommendation<>rh.recommendation_value
        ORDER BY date_recommended DESC LIMIT 1) h
    USING (ticker)
WHERE z.ticker NOT LIKE ALL(ARRAY['T.%','V.%'])
ORDER BY z.ticker;

execute s0;
explain verbose execute s0;

PREPARE

concat_ws

U.13 240124 6 comments23

W.14 240124 24 comments24

SELECT 2

QUERY PLAN

Merge Left Join  (cost=25094.56..26164.47 rows=32724 width=64)

  Output: concat_ws(' '::text, z.ticker, COALESCE(to_char((a.date_approved)::timestamp with time zone, 'YYMMDD'::text), to_char((z.date_recommended)::timestamp with time zone, 'YYMMDD'::text), '000000'::text), CASE WHEN (s.ticker IS NOT NULL) THEN 6 ELSE COALESCE(a.approved_recommendation, z.current_recommendation) END, replace(COALESCE(z.comments, 'N/C'::text), ' '::text, '_'::text), h.recommendation_value), z.ticker

  Merge Cond: (z.ticker = s.ticker)

  ->  Merge Left Join  (cost=25000.17..25076.36 rows=4812 width=84)

        Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, a.date_approved, a.approved_recommendation, h.recommendation_value

        Merge Cond: (z.ticker = a.ticker)

        ->  Sort  (cost=24916.80..24918.81 rows=802 width=76)

              Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, h.recommendation_value

              Sort Key: z.ticker

              ->  Nested Loop Left Join  (cost=30.97..24878.12 rows=802 width=76)

                    Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, h.recommendation_value

                    ->  Seq Scan on public.recommendations z  (cost=0.00..20.12 rows=802 width=72)

                          Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments

                          Filter: (z.ticker !~~ ALL ('{T.%,V.%}'::text[]))

                    ->  Subquery Scan on h  (cost=30.97..30.99 rows=1 width=36)

                          Output: h.ticker, h.recommendation_value, rh.date_recommended

                          Filter: (z.ticker = h.ticker)

                          ->  Limit  (cost=30.97..30.97 rows=1 width=40)

                                Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                ->  Sort  (cost=30.97..33.96 rows=1194 width=40)

                                      Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                      Sort Key: rh.date_recommended DESC

                                      ->  Seq Scan on public.recommendations_history rh  (cost=0.00..25.00 rows=1194 width=40)

                                            Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                            Filter: (z.current_recommendation <> rh.recommendation_value)

        ->  Sort  (cost=83.37..86.37 rows=1200 width=40)

              Output: a.date_approved, a.approved_recommendation, a.ticker

              Sort Key: a.ticker

              ->  Seq Scan on public.approved_recommendations a  (cost=0.00..22.00 rows=1200 width=40)

                    Output: a.date_approved, a.approved_recommendation, a.ticker

  ->  Sort  (cost=94.38..97.78 rows=1360 width=32)

        Output: s.ticker

        Sort Key: s.ticker

        ->  Seq Scan on public.scr_tickers s  (cost=0.00..23.60 rows=1360 width=32)

              Output: s.ticker

EXPLAIN

prepare s1 as 
SELECT concat_ws(' ', z.ticker,
    COALESCE(TO_CHAR(a.date_approved,   'YYMMDD'),
             TO_CHAR(z.date_recommended,'YYMMDD'),
             '000000'),
     CASE WHEN s.ticker IS NOT NULL THEN 6
          ELSE COALESCE(a.approved_recommendation, current_recommendation)
     END,
     REPLACE(COALESCE(comments,'N/C'),' ','_'),
     h.recommendation_value)
FROM recommendations z
LEFT JOIN (SELECT ticker FROM scr_tickers) s
       ON z.ticker = s.ticker
LEFT JOIN (SELECT ticker, date_approved, approved_recommendation
           FROM approved_recommendations) a
       ON z.ticker = a.ticker
LEFT JOIN LATERAL (SELECT rh.ticker, rh.recommendation_value
        FROM recommendations_history rh
        WHERE z.current_recommendation<>rh.recommendation_value
        ORDER BY date_recommended DESC LIMIT 1) h
    ON z.ticker = h.ticker
WHERE z.ticker NOT LIKE ALL(ARRAY['T.%','V.%'])
ORDER BY z.ticker;

execute s1;
explain verbose execute s1;

PREPARE

concat_ws

U.13 240124 6 comments23

W.14 240124 24 comments24

SELECT 2

QUERY PLAN

Merge Left Join  (cost=25094.56..26164.47 rows=32724 width=64)

  Output: concat_ws(' '::text, z.ticker, COALESCE(to_char((approved_recommendations.date_approved)::timestamp with time zone, 'YYMMDD'::text), to_char((z.date_recommended)::timestamp with time zone, 'YYMMDD'::text), '000000'::text), CASE WHEN (scr_tickers.ticker IS NOT NULL) THEN 6 ELSE COALESCE(approved_recommendations.approved_recommendation, z.current_recommendation) END, replace(COALESCE(z.comments, 'N/C'::text), ' '::text, '_'::text), h.recommendation_value), z.ticker

  Merge Cond: (z.ticker = scr_tickers.ticker)

  ->  Merge Left Join  (cost=25000.17..25076.36 rows=4812 width=84)

        Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, approved_recommendations.date_approved, approved_recommendations.approved_recommendation, h.recommendation_value

        Merge Cond: (z.ticker = approved_recommendations.ticker)

        ->  Sort  (cost=24916.80..24918.81 rows=802 width=76)

              Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, h.recommendation_value

              Sort Key: z.ticker

              ->  Nested Loop Left Join  (cost=30.97..24878.12 rows=802 width=76)

                    Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments, h.recommendation_value

                    ->  Seq Scan on public.recommendations z  (cost=0.00..20.12 rows=802 width=72)

                          Output: z.ticker, z.date_recommended, z.current_recommendation, z.comments

                          Filter: (z.ticker !~~ ALL ('{T.%,V.%}'::text[]))

                    ->  Subquery Scan on h  (cost=30.97..30.99 rows=1 width=36)

                          Output: h.ticker, h.recommendation_value, rh.date_recommended

                          Filter: (z.ticker = h.ticker)

                          ->  Limit  (cost=30.97..30.97 rows=1 width=40)

                                Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                ->  Sort  (cost=30.97..33.96 rows=1194 width=40)

                                      Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                      Sort Key: rh.date_recommended DESC

                                      ->  Seq Scan on public.recommendations_history rh  (cost=0.00..25.00 rows=1194 width=40)

                                            Output: rh.ticker, rh.recommendation_value, rh.date_recommended

                                            Filter: (z.current_recommendation <> rh.recommendation_value)

        ->  Sort  (cost=83.37..86.37 rows=1200 width=40)

              Output: approved_recommendations.date_approved, approved_recommendations.approved_recommendation, approved_recommendations.ticker

              Sort Key: approved_recommendations.ticker

              ->  Seq Scan on public.approved_recommendations  (cost=0.00..22.00 rows=1200 width=40)

                    Output: approved_recommendations.date_approved, approved_recommendations.approved_recommendation, approved_recommendations.ticker

  ->  Sort  (cost=94.38..97.78 rows=1360 width=32)

        Output: scr_tickers.ticker

        Sort Key: scr_tickers.ticker

        ->  Seq Scan on public.scr_tickers  (cost=0.00..23.60 rows=1360 width=32)

              Output: scr_tickers.ticker

EXPLAIN