--*****************************************************************************
--SELECT
--*****************************************************************************
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 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
limit 20

--*****************************************************************************
--Explain without limit
--*****************************************************************************
Sort  (cost=908238.42..910906.70 rows=1067313 width=809) (actual time=12.687..12.708 rows=316 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: quicksort  Memory: 343kB
  Buffers: shared hit=946
  ->  Nested Loop  (cost=2942.18..341714.36 rows=1067313 width=809) (actual time=10.046..12.465 rows=316 loops=1)
        Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
        Buffers: shared hit=946
        ->  HashAggregate  (cost=2941.62..2946.37 rows=475 width=951) (actual time=9.977..9.993 rows=25 loops=1)
              Output: o.jdata
              Group Key: (o.jdata ->> 'ossDn'::text)
              Buffers: shared hit=496
              ->  Nested Loop  (cost=3.14..2940.43 rows=475 width=951) (actual time=6.865..9.954 rows=25 loops=1)
                    Output: o.jdata, (o.jdata ->> 'ossDn'::text)
                    Buffers: shared hit=496
                    ->  Index Scan using idx_entity_jtype on tvcis.tvc_entity e  (cost=0.42..2396.65 rows=3 width=195) (actual time=6.827..9.875 rows=1 loops=1)
                          Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
                          Index Cond: ((e.jtype)::text = 'object'::text)
                          Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
                          Rows Removed by Filter: 2477
                          Buffers: shared hit=475
                    ->  Bitmap Heap Scan on tvcis.oss_objects o  (cost=2.71..179.68 rows=158 width=951) (actual time=0.025..0.053 rows=25 loops=1)
                          Output: o.id, o.jdata
                          Recheck Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                          Heap Blocks: exact=19
                          Buffers: shared hit=21
                          ->  Bitmap Index Scan on idx_oss_objects_sid  (cost=0.00..2.67 rows=158 width=0) (actual time=0.015..0.015 rows=25 loops=1)
                                Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                                Buffers: shared hit=2
        ->  Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a  (cost=0.56..143.72 rows=211 width=801) (actual time=0.018..0.033 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.881 ms
Execution time: 12.830 ms

--*****************************************************************************
--Explain with limit
--*****************************************************************************
Limit  (cost=3.57..414.17 rows=20 width=809) (actual time=2536.405..19044.847 rows=20 loops=1)
  Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
  Buffers: shared hit=4507507 read=1614
  ->  Nested Loop Semi Join  (cost=3.57..21912001.67 rows=1067313 width=809) (actual time=2536.404..19044.836 rows=20 loops=1)
        Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
        Join Filter: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
        Rows Removed by Join Filter: 10717797
        Buffers: shared hit=4507507 read=1614
        ->  Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a  (cost=0.43..1360616.52 rows=2134626 width=801) (actual time=0.021..577.152 rows=428721 loops=1)
              Output: a.id, a.jdata, a.ts_vector, a.ts_vector_fields
              Buffers: shared hit=317389 read=1614
        ->  Materialize  (cost=3.14..2942.81 rows=475 width=951) (actual time=0.000..0.002 rows=25 loops=428721)
              Output: o.jdata
              Buffers: shared hit=496
              ->  Nested Loop  (cost=3.14..2940.43 rows=475 width=951) (actual time=7.477..11.007 rows=25 loops=1)
                    Output: o.jdata
                    Buffers: shared hit=496
                    ->  Index Scan using idx_entity_jtype on tvcis.tvc_entity e  (cost=0.42..2396.65 rows=3 width=195) (actual time=7.453..10.943 rows=1 loops=1)
                          Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
                          Index Cond: ((e.jtype)::text = 'object'::text)
                          Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
                          Rows Removed by Filter: 2477
                          Buffers: shared hit=475
                    ->  Bitmap Heap Scan on tvcis.oss_objects o  (cost=2.71..179.68 rows=158 width=951) (actual time=0.021..0.055 rows=25 loops=1)
                          Output: o.id, o.jdata
                          Recheck Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                          Heap Blocks: exact=19
                          Buffers: shared hit=21
                          ->  Bitmap Index Scan on idx_oss_objects_sid  (cost=0.00..2.67 rows=158 width=0) (actual time=0.012..0.012 rows=25 loops=1)
                                Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
                                Buffers: shared hit=2
Planning time: 0.630 ms
Execution time: 19044.909 ms

--*****************************************************************************
--Data information
--*****************************************************************************
select o.jdata->>'ossDn'
from oss_objects o, tvc_entity e 
where e.jtype='object'    
and o.jdata->>'sid'=e.jdata->>'siteId'       
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%zaube%')

Rows: 25
Exec time: ~50msec



--*****************************************************************************
--data distribution
--*****************************************************************************
select count(*), 
	count(distinct a.jdata->>'dn'),
	count(distinct (tvc_convert_array_to_date(a.jdata -> 'alarmTime'))) 
from oss_alarms a;
=> 2128873;59684;1136120


--*****************************************************************************
-- Table: oss_alarms
--*****************************************************************************

-- DROP TABLE oss_alarms;

CREATE TABLE oss_alarms
(
  id bigint NOT NULL,
  jdata jsonb,
  ts_vector character varying,
  ts_vector_fields character varying,
  CONSTRAINT oss_alarms_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE oss_alarms
  OWNER TO tvcis;

-- Index: idx_alarms_consecnbr

-- DROP INDEX idx_alarms_consecnbr;

CREATE INDEX idx_alarms_consecnbr
  ON oss_alarms
  USING btree
  ((jdata ->> 'consecNbr'::text) COLLATE pg_catalog."default");

-- Index: idx_oss_alarms_alarm_time

-- DROP INDEX idx_oss_alarms_alarm_time;

CREATE INDEX idx_oss_alarms_alarm_time
  ON oss_alarms
  USING btree
  (tvc_convert_array_to_date(jdata -> 'alarmTime'::text) DESC);

-- Index: idx_oss_alarms_dn

-- DROP INDEX idx_oss_alarms_dn;

CREATE INDEX idx_oss_alarms_dn
  ON oss_alarms
  USING btree
  ((jdata ->> 'dn'::text) COLLATE pg_catalog."default");

-- Index: idx_oss_alarms_time_range

-- DROP INDEX idx_oss_alarms_time_range;

CREATE INDEX idx_oss_alarms_time_range
  ON oss_alarms
  USING gist
  (tsrange(tvc_convert_array_to_date(jdata -> 'alarmTime'::text), tvc_convert_array_to_date(jdata -> 'cancelTime'::text), '[]'::text));

-- Index: idx_oss_alarms_update_timestamp

-- DROP INDEX idx_oss_alarms_update_timestamp;

CREATE INDEX idx_oss_alarms_update_timestamp
  ON oss_alarms
  USING btree
  (tvc_convert_array_to_date(jdata -> 'updateTimestamp'::text) DESC);

-- Index: oss_alarms_textsearch_fields_idx

-- DROP INDEX oss_alarms_textsearch_fields_idx;

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

-- Index: oss_alarms_textsearch_idx

-- DROP INDEX oss_alarms_textsearch_idx;

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


-- Trigger: oss_alarms_briu on oss_alarms

-- DROP TRIGGER oss_alarms_briu ON oss_alarms;

CREATE TRIGGER oss_alarms_briu
  BEFORE INSERT OR UPDATE
  ON oss_alarms
  FOR EACH ROW
  EXECUTE PROCEDURE oss_alarms_briu();

-- Function: oss_alarms_briu()

-- DROP FUNCTION oss_alarms_briu();

CREATE OR REPLACE FUNCTION oss_alarms_briu()
  RETURNS trigger AS
$BODY$ begin 
 	NEW.ts_vector := jsonb_values_as_string(NEW.jdata); -- reurns json values without keys
 	NEW.ts_vector_fields := jsonb_values_as_string_with_fieldnames(NEW.jdata); -- returns json key/value pairs divided by words
 return new;
 end;

 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION oss_alarms_briu()
  OWNER TO tvcis;



--*****************************************************************************
-- oss_alarms.jdata structure (in java)
--*****************************************************************************
public class OssAlarmJson extends BaseJson {
    @Column(unique = true, nullable = false, length = 22, name = "CONSEC_NBR")
    private Long consecNbr;
    @Column(length = 22, name = "NE_GID")
    private Long neGid;
    @Column(nullable = false, length = 22, name = "ALARM_NUMBER")
    private Long alarmNumber;
    @Column(nullable = false, length = 7, name = "ALARM_TIME")
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
    private LocalDateTime alarmTime;
    @Column(length = 7, name = "CANCEL_TIME")
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
    private LocalDateTime cancelTime;
    @Column(length = 7, name = "ACK_TIME")
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
    private LocalDateTime ackTime;
    @Column(length = 22, name = "ACK_STATUS")
    private Long ackStatus;
    @Column(length = 65, name = "ACKED_BY")
    private String ackedBy;
    @Column(length = 1025, name = "TEXT")
    private String text;
    @Column(length = 401, name = "DN")
    private String dn;
    @Column(length = 1025, name = "USER_ADDITIONAL_INFO")
    private String userAdditionalInfo;
    @Column(length = 1025, name = "SUPPLEMENTARY_INFO")
    private String supplementaryInfo;
    @Column(nullable = false, length = 22, name = "ALARM_STATUS")
    private Long alarmStatus;
    @Column(nullable = false, length = 22, name = "SEVERITY")
    private Long severity;
    @Column(length = 65, name = "CANCELLED_BY")
    private String cancelledBy;
    @Column(length = 11, name = "UPDATE_TIMESTAMP")
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
    private LocalDateTime updateTimestamp;
    @Column(length = 1025, name = "DIAGNOSTIC_INFO")
    private String diagnosticInfo;
    @Column(length = 50, name = "DECODED_TYPE")
    private List<String> decodedType;
    @Column(length = 50, name = "TECH")
    private String tech;
    private Long commentId;
    private String dnSplit;
}