drop table tc;

create table tc(
 id                     numeric(38,0)                primary key,
 tname                  character varying(255)       not null,
 ag_id                  numeric(38,0)               ,
 tc                     character varying(255)       not null,
 status                 numeric(10,0)                not null,
 internal_status        numeric(10,0)                not null,
 create_date            timestamp(6) with time zone  not null,
 version                numeric(38,0)                not null,
 match_type             numeric(10,0)                not null default 0,
 c_id                   numeric(38,0)                not null,
 m_id                   numeric(38,0)                not null,
 a_id                   numeric(38,0)                not null,
 maxb                   numeric(18,6)               ,
 b_cc                   character varying(10)       ,
 ui_status              numeric(10,0)                not null default 0,
 destination_url        character varying(2084)     ,
 created_by             character varying(64)        not null,
 creation_date          timestamp(0) with time zone  not null default timezone('UTC'::text, clock_timestamp()),
 last_updated_by        character varying(64)        not null,
 last_updated_date      timestamp(0) with time zone  not null,
 pr                     numeric(5,0)                 not null default 0,
 ts                     numeric(1,0)                 not null default 0,
 uniqueness_hash_v2     numeric(29,0)                not null,
 pt                     numeric(5,0)                ,
 history                bigint                      ,
 t_secondary            text            CHECK (length(t_secondary) <= 4500)
);

insert into tc
select
  x as id,
  'tname_' || x as tname,
  case when x % 10 = 0 then null else x end as ag_id,
  'tc' as tc,
  0 as status,
  0 as internal_status,
  now() as create_date,
  42 as version,
  0 as match_type,
  (random()*1000)::int as c_id,
  x/4 as m_id,
  (random()*100)::int as a_id,
  0 as maxb,
  null as b_cc,
  0 as ui_status,
  null as destination_url,
  'c_b' as created_by,
  now() as creation_date,
  'l_u' as last_updated_by,
  now() as last_updated_date,
  0 as pr,
  0 as ts,
  x as uniqueness_hash_v2,
  x % 100 as pt,
  0 as history,
  null as t_secondary
from generate_series(1,1000000) x;

create index    "i_agi_tc_tcn" on tc (ag_id, tname);
create index    "i_cid_agid_tcn" on tc (c_id, ag_id, tname);
create index    "i_tc_adid_tid" on tc (a_id, id);
create index    "i_tc_advertiser_id" on tc (a_id);
create index    "i_tc_campaign_id" on tc (c_id);
create index    "i_tc_lud_agi" on tc (last_updated_date, ag_id);
create index    "i_tc_uniqueness_hash_v2" on tc (uniqueness_hash_v2);

vacuum analyze tc;

prepare p as 
SELECT count(*) FROM tc WHERE ((tc.a_id = $1)) AND ((tc.m_id = $2)) AND
((tc.ag_id is not null)) AND ((tc.id in ($3))) AND ((tc.pt in ($4, $5, $6)));

explain verbose execute p(0, 1, 42, 4,5,6);
