-- ****************************************************************
select o.jdata->>'ossDn'    
from oss_objects o, tvc_entity e 
where e.jtype='object'    
	and e.jdata->>'siteId' IS NOT NULL
	and o.jdata->>'sid'=e.jdata->>'siteId'       
	and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%zaube%')
-- ****************************************************************
	
	
Nested Loop  (cost=14.09..1894.09 rows=1 width=32) (actual time=6.473..10.349 rows=25 loops=1)
  Output: (o.jdata ->> 'ossDn'::text)
  Buffers: shared hit=453
  ->  Bitmap Heap Scan on tvcis.tvc_entity e  (cost=13.80..1834.65 rows=2 width=208) (actual time=6.451..10.279 rows=1 loops=1)
        Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
        Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
        Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
        Rows Removed by Filter: 1943
        Heap Blocks: exact=425
        Buffers: shared hit=432
        ->  Bitmap Index Scan on idx_tvc_entity_sid_u  (cost=0.00..13.80 rows=1265 width=0) (actual time=0.155..0.155 rows=1944 loops=1)
              Buffers: shared hit=7
  ->  Index Scan using idx_oss_objects_sid on tvcis.oss_objects o  (cost=0.29..29.49 rows=23 width=951) (actual time=0.017..0.050 rows=25 loops=1)
        Output: o.id, o.jdata
        Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
        Buffers: shared hit=21
Planning time: 0.502 ms
Execution time: 10.397 ms



-- ****************************************************************
select o.jdata->>'ossDn'    
from oss_objects o, tvc_entity e 
where e.jtype='object'    
	and e.jdata->>'siteId' IS NOT NULL
	and o.jdata->>'sid'=e.jdata->>'siteId'       
	and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%za%')
-- ****************************************************************

	
Gather  (cost=1014.16..5280.19 rows=9 width=32) (actual time=1.247..20.071 rows=1291 loops=1)
  Output: ((o.jdata ->> 'ossDn'::text))
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=1185
  ->  Nested Loop  (cost=14.16..4279.28 rows=5 width=32) (actual time=3.392..15.343 rows=646 loops=2)
        Output: (o.jdata ->> 'ossDn'::text)
        Buffers: shared hit=1548
        Worker 0: actual time=5.903..11.889 rows=187 loops=1
          Buffers: shared hit=363
        ->  Parallel Bitmap Heap Scan on tvcis.tvc_entity e  (cost=13.87..1694.08 rows=149 width=208) (actual time=3.234..12.305 rows=36 loops=2)
              Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
              Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
              Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%za%'::text)
              Rows Removed by Filter: 936
              Heap Blocks: exact=330
              Buffers: shared hit=544
              Worker 0: actual time=5.634..10.450 rows=13 loops=1
                Buffers: shared hit=207
              ->  Bitmap Index Scan on idx_tvc_entity_sid_u  (cost=0.00..13.80 rows=1265 width=0) (actual time=0.387..0.387 rows=1944 loops=1)
                    Buffers: shared hit=7
        ->  Index Scan using idx_oss_objects_sid on tvcis.oss_objects o  (cost=0.29..17.12 rows=23 width=951) (actual time=0.016..0.057 rows=18 loops=71)
              Output: o.id, o.jdata
              Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
              Buffers: shared hit=1004
              Worker 0: actual time=0.023..0.087 rows=14 loops=13
                Buffers: shared hit=156
Planning time: 0.542 ms
Execution time: 23.079 ms


-- ****************************************************************
select o.jdata->>'ossDn'    
from oss_objects o, tvc_entity e 
where e.jtype='object'    
	and e.jdata->>'siteId' IS NOT NULL
	and o.jdata->>'sid'=e.jdata->>'siteId'       
	and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%z%')
-- ****************************************************************

Gather  (cost=1014.41..6281.48 rows=44 width=32) (actual time=1.166..29.045 rows=5722 loops=1)
  Output: ((o.jdata ->> 'ossDn'::text))
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=3333 read=30
  ->  Nested Loop  (cost=14.41..5277.03 rows=26 width=32) (actual time=2.817..23.225 rows=2861 loops=2)
        Output: (o.jdata ->> 'ossDn'::text)
        Buffers: shared hit=5150 read=39
        Worker 0: actual time=4.802..21.169 rows=1943 loops=1
          Buffers: shared hit=1817 read=9
        ->  Parallel Bitmap Heap Scan on tvcis.tvc_entity e  (cost=14.12..1694.33 rows=744 width=208) (actual time=2.702..10.859 rows=176 loops=2)
              Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
              Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
              Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%z%'::text)
              Rows Removed by Filter: 796
              Heap Blocks: exact=292
              Buffers: shared hit=544
              Worker 0: actual time=4.754..10.699 rows=118 loops=1
                Buffers: shared hit=245
              ->  Bitmap Index Scan on idx_tvc_entity_sid_u  (cost=0.00..13.80 rows=1265 width=0) (actual time=0.329..0.329 rows=1944 loops=1)
                    Buffers: shared hit=7
        ->  Index Scan using idx_oss_objects_sid on tvcis.oss_objects o  (cost=0.29..4.59 rows=23 width=951) (actual time=0.013..0.048 rows=16 loops=353)
              Output: o.id, o.jdata
              Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
              Buffers: shared hit=4606 read=39
              Worker 0: actual time=0.015..0.066 rows=16 loops=118
                Buffers: shared hit=1572 read=9
Planning time: 0.530 ms
Execution time: 33.413 ms




-- ****************************************************************
-- full select after add one index and run full analyse on all tables
-- ****************************************************************
select a.id, a.jdata
from oss_alarms a
where 
                a.jdata->>'dn' in
                (
                    select o.jdata->>'ossDn'    
                    from oss_objects o, tvc_entity e 
                    where e.jtype='object'    
						and e.jdata->>'siteId' IS NOT NULL
                                                and o.jdata->>'sid'=e.jdata->>'siteId'       
                                                and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%zaube%')
                ) 
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) desc
offset 0 limit 20;
-- ****************************************************************

Limit  (cost=2057.69..2057.74 rows=20 width=809) (actual time=9.401..9.404 rows=20 loops=1)
  Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
  Buffers: shared hit=903
  ->  Sort  (cost=2057.69..2058.22 rows=211 width=809) (actual time=9.400..9.401 rows=20 loops=1)
        Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
        Sort Key: (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))) DESC
        Sort Method: top-N heapsort  Memory: 58kB
        Buffers: shared hit=903
        ->  Nested Loop  (cost=1894.65..2052.08 rows=211 width=809) (actual time=6.996..9.261 rows=316 loops=1)
              Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
              Buffers: shared hit=903
              ->  HashAggregate  (cost=1894.09..1894.10 rows=1 width=951) (actual time=6.949..6.955 rows=25 loops=1)
                    Output: o.jdata
                    Group Key: (o.jdata ->> 'ossDn'::text)
                    Buffers: shared hit=453
                    ->  Nested Loop  (cost=14.09..1894.09 rows=1 width=951) (actual time=4.406..6.930 rows=25 loops=1)
                          Output: o.jdata, (o.jdata ->> 'ossDn'::text)
                          Buffers: shared hit=453
                          ->  Bitmap Heap Scan on tvcis.tvc_entity e  (cost=13.80..1834.65 rows=2 width=208) (actual time=4.393..6.878 rows=1 loops=1)
                                Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
                                Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
                                Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
                                Rows Removed by Filter: 1943
                                Heap Blocks: exact=425
                                Buffers: shared hit=432
                                ->  Bitmap Index Scan on idx_tvc_entity_sid_u  (cost=0.00..13.80 rows=1265 width=0) (actual time=0.102..0.102 rows=1944 loops=1)
                                      Buffers: shared hit=7
                          ->  Index Scan using idx_oss_objects_sid on tvcis.oss_objects o  (cost=0.29..29.49 rows=23 width=951) (actual time=0.010..0.038 rows=25 loops=1)
                                Output: o.id, o.jdata
                                Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                                Buffers: shared hit=21
              ->  Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a  (cost=0.56..102.59 rows=211 width=801) (actual time=0.016..0.035 rows=13 loops=25)
                    Output: a.id, a.jdata, a.ts_vector, a.ts_vector_fields
                    Index Cond: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
                    Buffers: shared hit=410
Planning time: 0.992 ms
Execution time: 9.462 ms


-- ****************************************************************
select a.id, a.jdata
from oss_alarms a
where 
                a.jdata->>'dn' in
                (
                    select o.jdata->>'ossDn'    
                    from oss_objects o, tvc_entity e 
                    where e.jtype='object'    
						and e.jdata->>'siteId' IS NOT NULL
                                                and o.jdata->>'sid'=e.jdata->>'siteId'       
                                                and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%za%')
                ) 
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) desc
offset 0 limit 20;
-- ****************************************************************
Limit  (cost=7649.11..7649.16 rows=20 width=809) (actual time=81409.102..81409.108 rows=20 loops=1)
  Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
  Buffers: shared hit=40048 read=7044 dirtied=9
  ->  Sort  (cost=7649.11..7653.87 rows=1903 width=809) (actual time=81409.101..81409.104 rows=20 loops=1)
        Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
        Sort Key: (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))) DESC
        Sort Method: top-N heapsort  Memory: 58kB
        Buffers: shared hit=40048 read=7044 dirtied=9
        ->  Nested Loop  (cost=6176.13..7598.47 rows=1903 width=809) (actual time=18.561..81384.112 rows=37312 loops=1)
              Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
              Buffers: shared hit=40048 read=7044 dirtied=9
              ->  HashAggregate  (cost=6175.57..6175.66 rows=9 width=951) (actual time=18.491..19.704 rows=1291 loops=1)
                    Output: o.jdata
                    Group Key: (o.jdata ->> 'ossDn'::text)
                    Buffers: shared hit=2705
                    ->  Nested Loop  (cost=0.57..6175.55 rows=9 width=951) (actual time=0.112..17.355 rows=1291 loops=1)
                          Output: o.jdata, (o.jdata ->> 'ossDn'::text)
                          Buffers: shared hit=2705
                          ->  Index Scan using idx_tvc_entity_sid_u on tvcis.tvc_entity e  (cost=0.28..1856.72 rows=253 width=208) (actual time=0.091..13.944 rows=71 loops=1)
                                Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
                                Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%za%'::text)
                                Rows Removed by Filter: 1873
                                Buffers: shared hit=1702
                          ->  Index Scan using idx_oss_objects_sid on tvcis.oss_objects o  (cost=0.29..16.84 rows=23 width=951) (actual time=0.009..0.033 rows=18 loops=71)
                                Output: o.id, o.jdata
                                Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                                Buffers: shared hit=1003
              ->  Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a  (cost=0.56..102.59 rows=211 width=801) (actual time=2.320..62.536 rows=29 loops=1291)
                    Output: a.id, a.jdata, a.ts_vector, a.ts_vector_fields
                    Index Cond: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
                    Buffers: shared hit=33869 read=7028 dirtied=9
Planning time: 1.593 ms
Execution time: 81409.196 ms



-- ****************************************************************
-- Table: oss_objects
-- ****************************************************************

-- DROP TABLE oss_objects;

CREATE TABLE oss_objects
(
  id bigint NOT NULL,
  jdata jsonb,
  CONSTRAINT oss_objects_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE oss_objects
  OWNER TO tvcis;

-- Index: idx_objects_gid

-- DROP INDEX idx_objects_gid;

CREATE INDEX idx_objects_gid
  ON oss_objects
  USING btree
  ((jdata ->> 'gid'::text) COLLATE pg_catalog."default");

-- Index: idx_objects_ossdn

-- DROP INDEX idx_objects_ossdn;

CREATE INDEX idx_objects_ossdn
  ON oss_objects
  USING btree
  ((jdata ->> 'ossDn'::text) COLLATE pg_catalog."default");

-- Index: idx_oss_objects_sid

-- DROP INDEX idx_oss_objects_sid;

CREATE INDEX idx_oss_objects_sid
  ON oss_objects
  USING btree
  ((jdata ->> 'sid'::text) COLLATE pg_catalog."default");

  
-- ****************************************************************
-- Table: tvc_entity
-- ****************************************************************

-- DROP TABLE tvc_entity;

CREATE TABLE tvc_entity
(
  id bigint NOT NULL,
  jtype character varying(64) NOT NULL,
  jdata jsonb,
  ts_vector character varying,
  jmetadata jsonb,
  ts_vector_fields character varying,
  CONSTRAINT tvc_entity_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tvc_entity
  OWNER TO tvcis;

-- Index: idx_entity_jdata

-- DROP INDEX idx_entity_jdata;

CREATE INDEX idx_entity_jdata
  ON tvc_entity
  USING gin
  (jdata);

-- Index: idx_entity_jtype

-- DROP INDEX idx_entity_jtype;

CREATE INDEX idx_entity_jtype
  ON tvc_entity
  USING btree
  (jtype COLLATE pg_catalog."default");

-- Index: idx_entity_meta_createdate

-- DROP INDEX idx_entity_meta_createdate;

CREATE INDEX idx_entity_meta_createdate
  ON tvc_entity
  USING btree
  ((jmetadata -> 'createDate'::text));

-- Index: idx_entity_meta_lasteditdate

-- DROP INDEX idx_entity_meta_lasteditdate;

CREATE INDEX idx_entity_meta_lasteditdate
  ON tvc_entity
  USING btree
  ((jmetadata -> 'lastEditDate'::text));

-- Index: idx_entity_name

-- DROP INDEX idx_entity_name;

CREATE INDEX idx_entity_name
  ON tvc_entity
  USING btree
  ((jdata ->> 'name'::text) COLLATE pg_catalog."default");

-- Index: idx_tvc_entity_enddate

-- DROP INDEX idx_tvc_entity_enddate;

CREATE INDEX idx_tvc_entity_enddate
  ON tvc_entity
  USING btree
  (tvc_convert_array_to_date(jdata -> 'endDate'::text));

-- Index: idx_tvc_entity_planneddate

-- DROP INDEX idx_tvc_entity_planneddate;

CREATE INDEX idx_tvc_entity_planneddate
  ON tvc_entity
  USING btree
  (tvc_convert_array_to_date(jdata -> 'plannedDate'::text));

-- Index: idx_tvc_entity_sid_u

-- DROP INDEX idx_tvc_entity_sid_u;

CREATE UNIQUE INDEX idx_tvc_entity_sid_u
  ON tvc_entity
  USING btree
  ((jdata ->> 'siteId'::text) COLLATE pg_catalog."default")
  WHERE (jdata ->> 'siteId'::text) IS NOT NULL;

-- Index: idx_tvc_entity_startdate

-- DROP INDEX idx_tvc_entity_startdate;

CREATE INDEX idx_tvc_entity_startdate
  ON tvc_entity
  USING btree
  (tvc_convert_array_to_date(jdata -> 'startDate'::text));

-- Index: idx_tvc_entity_startdate_enddate

-- DROP INDEX idx_tvc_entity_startdate_enddate;

CREATE INDEX idx_tvc_entity_startdate_enddate
  ON tvc_entity
  USING gist
  (tsrange(tvc_convert_array_to_date(jdata -> 'startDate'::text), tvc_convert_array_to_date(jdata -> 'endDate'::text), '[]'::text));

-- Index: idx_tvc_entity_startdate_planneddate

-- DROP INDEX idx_tvc_entity_startdate_planneddate;

CREATE INDEX idx_tvc_entity_startdate_planneddate
  ON tvc_entity
  USING gist
  (tsrange(tvc_convert_array_to_date(jdata -> 'startDate'::text), tvc_convert_array_to_date(jdata -> 'plannedDate'::text), '[]'::text));

-- Index: tvc_entity_textsearch_fields_idx

-- DROP INDEX tvc_entity_textsearch_fields_idx;

CREATE INDEX tvc_entity_textsearch_fields_idx
  ON tvc_entity
  USING gin
  (to_tsvector('simple'::regconfig, ts_vector_fields::text));

-- Index: tvc_entity_textsearch_idx

-- DROP INDEX tvc_entity_textsearch_idx;

CREATE INDEX tvc_entity_textsearch_idx
  ON tvc_entity
  USING gin
  (to_tsvector('simple'::regconfig, ts_vector::text));


-- Trigger: tvc_entity_audit on tvc_entity

-- DROP TRIGGER tvc_entity_audit ON tvc_entity;

CREATE TRIGGER tvc_entity_audit
  BEFORE INSERT OR UPDATE OR DELETE
  ON tvc_entity
  FOR EACH ROW
  EXECUTE PROCEDURE tvc_entity_audit();

-- Trigger: tvc_entity_briu on tvc_entity

-- DROP TRIGGER tvc_entity_briu ON tvc_entity;

CREATE TRIGGER tvc_entity_briu
  BEFORE INSERT OR UPDATE
  ON tvc_entity
  FOR EACH ROW
  EXECUTE PROCEDURE tvc_entity_briu();


  
-- ****************************************************************
-- Bed example
-- ****************************************************************

select a.id, a.jdata 
from tvcis.oss_alarms a 
where (
		to_tsvector(
			'simple'::regconfig,
			a.ts_vector_fields
		) @@ to_tsquery(
			'simple',
			' (decodedType5b6f0753ossAlarmsGenerator) '
		)
) 
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) DESC 
limit 20;
-- ****************************************************************
Without limit:
Sort  (cost=8787.37..8799.58 rows=4885 width=809) (actual time=7.351..7.423 rows=586 loops=1)
  Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
  Sort Key: (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))) DESC
  Sort Method: quicksort  Memory: 620kB
  Buffers: shared hit=731
  ->  Bitmap Heap Scan on tvcis.oss_alarms a  (cost=233.46..8488.06 rows=4885 width=809) (actual time=1.402..6.772 rows=586 loops=1)
        Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
        Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
        Heap Blocks: exact=570
        Buffers: shared hit=731
        ->  Bitmap Index Scan on oss_alarms_textsearch_fields_idx  (cost=0.00..232.24 rows=4885 width=0) (actual time=1.188..1.188 rows=587 loops=1)
              Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
              Buffers: shared hit=161
Planning time: 0.301 ms
Execution time: 7.574 ms
-- ****************************************************************
With limit:
Limit  (cost=0.43..5769.24 rows=20 width=809) (actual time=785.691..4972.198 rows=20 loops=1)
  Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
  Buffers: shared hit=24207
  ->  Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a  (cost=0.43..1409033.18 rows=4885 width=809) (actual time=785.689..4972.186 rows=20 loops=1)
        Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
        Filter: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
        Rows Removed by Filter: 32588
        Buffers: shared hit=24207
Planning time: 0.312 ms
Execution time: 4972.245 ms

-- ****************************************************************
-- Good example
-- ****************************************************************
select a.id, a.jdata 
from tvcis.oss_alarms a 
where (
		to_tsvector(
			'simple'::regconfig,
			a.ts_vector_fields
		) @@ to_tsquery(
			'simple',
			' (decodedType5b6f0753ossAlarmsCore) '
		)
) 
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) DESC 
limit 20;
-- ****************************************************************
Without limit:
Too many rows to test
-- ****************************************************************
With limit:
Limit  (cost=0.43..52.67 rows=20 width=809) (actual time=0.358..43.088 rows=20 loops=1)
  Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
  Buffers: shared hit=93
  ->  Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a  (cost=0.43..1558460.15 rows=596675 width=809) (actual time=0.356..43.080 rows=20 loops=1)
        Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
        Filter: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
        Rows Removed by Filter: 107
        Buffers: shared hit=93
Planning time: 0.370 ms
Execution time: 43.131 ms

-- ****************************************************************
-- count with many hits
select count(*)
from tvcis.oss_alarms a 
where (
		to_tsvector(
			'simple'::regconfig,
			a.ts_vector_fields
		) @@ to_tsquery(
			'simple',
			' (decodedType5b6f0753ossAlarmsCore) '
		)
) 
-- ****************************************************************
Finalize Aggregate  (cost=456867.84..456867.85 rows=1 width=8) (actual time=176328.408..176328.408 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=13201 read=89899
  ->  Gather  (cost=456867.63..456867.84 rows=2 width=8) (actual time=176328.346..176328.391 rows=3 loops=1)
        Output: (PARTIAL count(*))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=13201 read=89899
        ->  Partial Aggregate  (cost=455867.63..455867.64 rows=1 width=8) (actual time=175785.805..175785.806 rows=1 loops=3)
              Output: PARTIAL count(*)
              Buffers: shared hit=39539 read=267264
              Worker 0: actual time=175514.664..175514.664 rows=1 loops=1
                Buffers: shared hit=13449 read=88968
              Worker 1: actual time=175514.661..175514.661 rows=1 loops=1
                Buffers: shared hit=12889 read=88397
              ->  Parallel Bitmap Heap Scan on tvcis.oss_alarms a  (cost=5080.23..455246.09 rows=248615 width=0) (actual time=1142.280..175706.097 rows=199195 loops=3)
                    Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
                    Rows Removed by Index Recheck: 110314
                    Heap Blocks: exact=31345 lossy=57106
                    Buffers: shared hit=39539 read=267264
                    Worker 0: actual time=926.364..175435.071 rows=198772 loops=1
                      Buffers: shared hit=13449 read=88968
                    Worker 1: actual time=926.401..175435.211 rows=197990 loops=1
                      Buffers: shared hit=12889 read=88397
                    ->  Bitmap Index Scan on oss_alarms_textsearch_fields_idx  (cost=0.00..4931.06 rows=596675 width=0) (actual time=1549.231..1549.231 rows=598899 loops=1)
                          Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
                          Buffers: shared hit=1 read=274
Planning time: 0.236 ms
Execution time: 176343.730 ms
-- ****************************************************************
-- count with few hits
select count(*)
from tvcis.oss_alarms a 
where (
		to_tsvector(
			'simple'::regconfig,
			a.ts_vector_fields
		) @@ to_tsquery(
			'simple',
			' (decodedType5b6f0753ossAlarmsGenerator) '
		)
) 
-- ****************************************************************
Aggregate  (cost=7266.81..7266.82 rows=1 width=8) (actual time=1.843..1.843 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=731
  ->  Bitmap Heap Scan on tvcis.oss_alarms a  (cost=233.46..7254.60 rows=4885 width=0) (actual time=0.823..1.775 rows=586 loops=1)
        Output: id, jdata, ts_vector, ts_vector_fields
        Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
        Heap Blocks: exact=570
        Buffers: shared hit=731
        ->  Bitmap Index Scan on oss_alarms_textsearch_fields_idx  (cost=0.00..232.24 rows=4885 width=0) (actual time=0.765..0.765 rows=587 loops=1)
              Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
              Buffers: shared hit=161
Planning time: 0.248 ms
Execution time: 1.883 ms