explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GbbD

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 26,635.548 ↑ 1.0 7 1

Unique (cost=529,228.54..529,229.83 rows=7 width=1,156) (actual time=26,635.502..26,635.548 rows=7 loops=1)

2. 0.091 26,635.503 ↑ 1.0 7 1

Sort (cost=529,228.54..529,228.56 rows=7 width=1,156) (actual time=26,635.501..26,635.503 rows=7 loops=1)

  • Sort Key: loc_region_telkom_id, (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date)) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text)) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN (date(start_dtm) < '2020-08-01'::date) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((start_dtm)::text ~~ '2020-08%'::text) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50)) THEN wo_detail_id ELSE NULL::character varying END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 4) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 4) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 4)) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 4)) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN ((date(start_dtm) < '2020-08-01'::date) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((start_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50) AND (activity_id = 4)) THEN wo_detail_id ELSE NULL::character varying END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 5) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 5) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 5)) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 5)) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN ((date(start_dtm) < '2020-08-01'::date) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((start_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50) AND (activity_id = 5)) THEN wo_detail_id ELSE NULL::character varying END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 6) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 6) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 6)) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 6)) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN ((date(start_dtm) < '2020-08-01'::date) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((start_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50) AND (activity_id = 6)) THEN wo_detail_id ELSE NULL::character varying END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = ANY ('{7,2}'::integer[])) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = ANY ('{7,2}'::integer[])) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN ((date(start_dtm) < '2020-08-01'::date) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((start_dtm)::text ~~ '2020-08%'::text) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50) AND (activity_id = ANY ('{7,2}'::integer[]))) THEN wo_detail_id ELSE NULL::character varying END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 8) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 8) AND ((satuan)::text = 'ODP'::text)) THEN result ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 8)) THEN (result * performance) ELSE '0'::numeric END)), (sum(CASE WHEN ((status_id = 50) AND ((end_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 8)) THEN (result * performance) ELSE '0'::numeric END)), (count(DISTINCT CASE WHEN ((date(start_dtm) < '2020-08-01'::date) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((start_dtm)::text ~~ '2020-08%'::text) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 30) AND (date(submit_dtm) < '2020-08-01'::date) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN ((status_id = 50) AND (date(end_dtm) < '2020-08-01'::date) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 30) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND (date(start_dtm) < '2020-08-01'::date) AND (status_id = 50) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((submit_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 30) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END)), (count(DISTINCT CASE WHEN (((end_dtm)::text ~~ '2020-08%'::text) AND ((start_dtm)::text ~~ '2020-08%'::text) AND (status_id = 50) AND (activity_id = 8)) THEN wo_detail_id ELSE NULL::character varying END))
  • Sort Method: quicksort Memory: 32kB
3. 26,135.224 26,635.412 ↑ 1.0 7 1

GroupAggregate (cost=69,690.47..529,228.44 rows=7 width=1,156) (actual time=4,688.126..26,635.412 rows=7 loops=1)

  • Group Key: loc_region_telkom_id
4. 275.194 500.188 ↓ 1.0 408,646 1

Sort (cost=69,690.47..70,680.85 rows=396,153 width=67) (actual time=357.612..500.188 rows=408,646 loops=1)

  • Sort Key: loc_region_telkom_id
  • Sort Method: quicksort Memory: 69,674kB
5. 224.994 224.994 ↓ 1.0 408,646 1

Seq Scan on t_report_performansi tp (cost=0.00..32,856.76 rows=396,153 width=67) (actual time=0.011..224.994 rows=408,646 loops=1)

  • Filter: (user_loc_id <> 0)
  • Rows Removed by Filter: 13,032
Planning time : 1.572 ms
Execution time : 26,644.345 ms