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 analyze 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) (actual time=0.069..0.074 rows=2 loops=1)

  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) (actual time=0.057..0.060 rows=2 loops=1)

        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) (actual time=0.043..0.044 rows=2 loops=1)

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

              Sort Key: z.ticker

              Sort Method: quicksort  Memory: 25kB

              ->  Nested Loop Left Join  (cost=30.97..24878.12 rows=802 width=76) (actual time=0.024..0.030 rows=2 loops=1)

                    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) (actual time=0.006..0.007 rows=2 loops=1)

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

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

                          Rows Removed by Filter: 2

                    ->  Subquery Scan on h  (cost=30.97..30.99 rows=1 width=36) (actual time=0.010..0.010 rows=0 loops=2)

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

                          Filter: (z.ticker = h.ticker)

                          Rows Removed by Filter: 0

                          ->  Limit  (cost=30.97..30.97 rows=1 width=40) (actual time=0.009..0.009 rows=0 loops=2)

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

                                ->  Sort  (cost=30.97..33.96 rows=1194 width=40) (actual time=0.008..0.008 rows=0 loops=2)

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

                                      Sort Key: rh.date_recommended DESC

                                      Sort Method: quicksort  Memory: 25kB

                                      ->  Seq Scan on public.recommendations_history rh  (cost=0.00..25.00 rows=1194 width=40) (actual time=0.002..0.002 rows=0 loops=2)

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

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

                                            Rows Removed by Filter: 0

        ->  Sort  (cost=83.37..86.37 rows=1200 width=40) (actual time=0.013..0.013 rows=1 loops=1)

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

              Sort Key: a.ticker

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on public.approved_recommendations a  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.011..0.011 rows=1 loops=1)

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

  ->  Sort  (cost=94.38..97.78 rows=1360 width=32) (actual time=0.004..0.004 rows=1 loops=1)

        Output: s.ticker

        Sort Key: s.ticker

        Sort Method: quicksort  Memory: 25kB

        ->  Seq Scan on public.scr_tickers s  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.001..0.002 rows=1 loops=1)

              Output: s.ticker

Planning Time: 0.002 ms

Execution Time: 0.104 ms

EXPLAIN

select ticker, ticker NOT LIKE ALL(ARRAY['T.%','V.%']) cond
  from (values('T.123'),('V.123'),('Y.123'))_(ticker);

ticker
cond

T.123
f

V.123
f

Y.123
t

SELECT 3

select ticker, ticker NOT LIKE ALL(VALUES('T.%'),('V.%')) cond
  from (values('T.123'),('V.123'),('Y.123'))_(ticker);

ticker
cond

T.123
f

V.123
f

Y.123
t

SELECT 3