explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yLpR

Settings
# exclusive inclusive rows x rows loops node
1. 86.814 15,140.223 ↓ 22,638.0 22,638 1

Sort (cost=287,574.71..287,574.71 rows=1 width=148) (actual time=15,110.084..15,140.223 rows=22,638 loops=1)

  • Sort Key: (date_trunc('hour'::text, (view_erlang_minute_per_drop_sdcch.rectimestamp + '05:30:00'::interval))), public.kpissa.ssaname, public.kpissa.omcip, public.omcmasterinfo.nename, (CASE WHEN (sitemapping.sitename IS NULL) THEN (sitemapping.sitename)::text ELSE pg_catalog.concat(sitemapping.sitename, '-', sitemaster.sitenumber) END), sitemapping.cellname, view_erlang_minute_per_drop_sdcch.cellid
  • Sort Method: external merge Disk: 2616kB
2. 101.972 15,053.409 ↓ 22,638.0 22,638 1

HashAggregate (cost=287,574.67..287,574.70 rows=1 width=148) (actual time=15,032.671..15,053.409 rows=22,638 loops=1)

3. 64.067 14,951.437 ↓ 38,865.0 38,865 1

Nested Loop Left Join (cost=287,211.97..287,574.64 rows=1 width=148) (actual time=3,484.298..14,951.437 rows=38,865 loops=1)

4. 11.335 14,887.370 ↓ 38,865.0 38,865 1

Nested Loop (cost=287,211.97..287,574.35 rows=1 width=148) (actual time=3,484.266..14,887.370 rows=38,865 loops=1)

5. 29.718 14,754.721 ↓ 40,438.0 40,438 1

Nested Loop Left Join (cost=287,211.97..287,573.95 rows=1 width=128) (actual time=3,484.254..14,754.721 rows=40,438 loops=1)

6. 15.493 3,523.677 ↓ 40,438.0 40,438 1

Merge Join (cost=287,211.97..287,213.48 rows=1 width=102) (actual time=3,483.497..3,523.677 rows=40,438 loops=1)

  • Merge Cond: (public.omcmasterinfo.seq_no = view_erlang_minute_per_drop_sdcch.omcid)
7. 0.137 0.219 ↑ 1.0 299 1

Sort (cost=20.28..21.03 rows=299 width=38) (actual time=0.160..0.219 rows=299 loops=1)

  • Sort Key: public.omcmasterinfo.seq_no
  • Sort Method: quicksort Memory: 49kB
8. 0.082 0.082 ↑ 1.0 299 1

Seq Scan on omcmasterinfo (cost=0.00..7.99 rows=299 width=38) (actual time=0.016..0.082 rows=299 loops=1)

9. 88.283 3,507.965 ↓ 40,439.0 40,439 1

Sort (cost=287,191.69..287,191.69 rows=1 width=68) (actual time=3,483.325..3,507.965 rows=40,439 loops=1)

  • Sort Key: view_erlang_minute_per_drop_sdcch.omcid
  • Sort Method: external sort Disk: 4080kB
10. 9.219 3,419.682 ↓ 72,555.0 72,555 1

Subquery Scan on view_erlang_minute_per_drop_sdcch (cost=287,191.63..287,191.68 rows=1 width=68) (actual time=3,358.618..3,419.682 rows=72,555 loops=1)

11. 36.384 3,410.463 ↓ 72,555.0 72,555 1

GroupAggregate (cost=287,191.63..287,191.67 rows=1 width=97) (actual time=3,358.618..3,410.463 rows=72,555 loops=1)

12. 115.164 3,374.079 ↓ 72,555.0 72,555 1

Sort (cost=287,191.63..287,191.63 rows=1 width=97) (actual time=3,358.611..3,374.079 rows=72,555 loops=1)

  • Sort Key: public.bsckpierlangtable.rectimestamp, public.bsckpierlangtable.omcid, public.bsckpierlangtable.lac, public.bsckpierlangtable.neinfo_id, public.bsckpierlangtable.nename, public.bsckpierlangtable.cellid, public.bsckpierlangtable.trxid, public.bsckpierlangtable.tchcodec, public.bsckpilaccelltable.rectimestamp
  • Sort Method: external sort Disk: 6152kB
13. 110.373 3,258.915 ↓ 72,555.0 72,555 1

Merge Join (cost=258,738.10..287,191.62 rows=1 width=97) (actual time=1,999.156..3,258.915 rows=72,555 loops=1)

  • Merge Cond: ((public.bsckpilaccelltable.rectimestamp = public.bsckpierlangtable.rectimestamp) AND (public.bsckpilaccelltable.omcid = public.bsckpierlangtable.omcid) AND (public.bsckpilaccelltable.lac = public.bsckpierlangtable.lac) AND (public.bsckpilaccelltable.cellid = public.bsckpierlangtable.cellid) AND (public.bsckpilaccelltable.trxid = public.bsckpierlangtable.trxid) AND (public.bsckpilaccelltable.tchcodec = public.bsckpierlangtable.tchcodec) AND (public.bsckpilaccelltable.neinfo_id = public.bsckpierlangtable.neinfo_id) AND ((public.bsckpilaccelltable.nename)::text = (public.bsckpierlangtable.nename)::text))
14. 780.901 1,763.887 ↓ 7.0 287,810 1

GroupAggregate (cost=114,242.97..130,566.81 rows=41,067 width=97) (actual time=870.955..1,763.887 rows=287,810 loops=1)

15. 608.795 982.986 ↑ 1.0 404,901 1

Sort (cost=114,242.97..115,269.63 rows=410,662 width=97) (actual time=870.928..982.986 rows=404,901 loops=1)

  • Sort Key: public.bsckpilaccelltable.rectimestamp, public.bsckpilaccelltable.omcid, public.bsckpilaccelltable.lac, public.bsckpilaccelltable.cellid, public.bsckpilaccelltable.trxid, public.bsckpilaccelltable.tchcodec, public.bsckpilaccelltable.neinfo_id, public.bsckpilaccelltable.nename
  • Sort Method: external merge Disk: 45072kB
16. 55.065 374.191 ↑ 1.0 404,906 1

Result (cost=0.00..31,034.69 rows=410,662 width=97) (actual time=0.031..374.191 rows=404,906 loops=1)

17. 36.732 319.126 ↑ 1.0 404,906 1

Append (cost=0.00..31,034.69 rows=410,662 width=97) (actual time=0.031..319.126 rows=404,906 loops=1)

18. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on bsckpilaccelltable (cost=0.00..0.00 rows=1 width=128) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((rectimestamp >= '2018-05-01 04:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-02 04:29:59.999'::timestamp without time zone))
19. 282.394 282.394 ↑ 1.0 404,906 1

Index Scan using bsckpilaccelltable_052018_rectime on bsckpilaccelltable_052018 bsckpilaccelltable (cost=0.00..31,034.69 rows=410,661 width=97) (actual time=0.031..282.394 rows=404,906 loops=1)

  • Index Cond: ((rectimestamp >= '2018-05-01 04:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-02 04:29:59.999'::timestamp without time zone))
20. 19.481 1,384.655 ↓ 2.8 72,555 1

Materialize (cost=144,495.12..154,876.91 rows=25,794 width=57) (actual time=1,128.195..1,384.655 rows=72,555 loops=1)

21. 161.901 1,365.174 ↓ 2.8 72,555 1

GroupAggregate (cost=144,495.12..154,554.48 rows=25,794 width=65) (actual time=1,128.187..1,365.174 rows=72,555 loops=1)

22. 424.118 1,203.273 ↑ 1.0 256,235 1

Sort (cost=144,495.12..145,139.95 rows=257,932 width=65) (actual time=1,128.157..1,203.273 rows=256,235 loops=1)

  • Sort Key: public.bsckpierlangtable.rectimestamp, public.bsckpierlangtable.omcid, public.bsckpierlangtable.lac, public.bsckpierlangtable.cellid, public.bsckpierlangtable.trxid, public.bsckpierlangtable.tchcodec, public.bsckpierlangtable.neinfo_id, public.bsckpierlangtable.nename
  • Sort Method: external merge Disk: 20544kB
23. 35.456 779.155 ↑ 1.0 256,235 1

Result (cost=0.00..100,150.38 rows=257,932 width=65) (actual time=0.084..779.155 rows=256,235 loops=1)

24. 25.685 743.699 ↑ 1.0 256,235 1

Append (cost=0.00..100,150.38 rows=257,932 width=65) (actual time=0.084..743.699 rows=256,235 loops=1)

25. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on bsckpierlangtable (cost=0.00..0.00 rows=1 width=96) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((chantype = ANY ('{4,8}'::bigint[])) AND (cellid >= 0::bigint) AND (lac >= 0::bigint) AND (rectimestamp >= '2018-05-01 04:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-02 04:29:59.999'::timestamp without time zone) AND (releasetype = 1))
26. 718.013 718.013 ↑ 1.0 256,235 1

Index Scan using bsckpierlangtable_052018_rectime on bsckpierlangtable_052018 bsckpierlangtable (cost=0.00..100,150.38 rows=257,931 width=65) (actual time=0.082..718.013 rows=256,235 loops=1)

  • Index Cond: ((rectimestamp >= '2018-05-01 04:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-02 04:29:59.999'::timestamp without time zone))
  • Filter: ((chantype = ANY ('{4,8}'::bigint[])) AND (cellid >= 0::bigint) AND (lac >= 0::bigint) AND (releasetype = 1))
  • Rows Removed by Filter: 1251069
27. 18.715 11,201.326 ↓ 0.0 0 40,438

Nested Loop (cost=0.00..360.45 rows=1 width=50) (actual time=0.267..0.277 rows=0 loops=40,438)

  • Join Filter: ((public.omcmasterinfo.neip)::text = (public.kpissa.neip)::text)
28. 38.371 11,160.888 ↓ 0.0 0 40,438

Nested Loop (cost=0.00..360.17 rows=1 width=72) (actual time=0.266..0.276 rows=0 loops=40,438)

  • Join Filter: (view_erlang_minute_per_drop_sdcch.omcid = public.omcmasterinfo.seq_no)
  • Rows Removed by Join Filter: 1
29. 10,594.756 10,594.756 ↑ 1.0 1 40,438

Index Scan using sitemapping_pkey on sitemapping (cost=0.00..357.64 rows=1 width=57) (actual time=0.194..0.262 rows=1 loops=40,438)

  • Index Cond: ((view_erlang_minute_per_drop_sdcch.lac = lacid) AND (view_erlang_minute_per_drop_sdcch.cellid = cellid))
30. 527.761 527.761 ↑ 1.0 1 40,597

Index Scan using omcmasterinfo_pkey on omcmasterinfo (cost=0.00..2.51 rows=1 width=15) (actual time=0.009..0.013 rows=1 loops=40,597)

  • Index Cond: ((neip)::text = (sitemapping.neip)::text)
31. 21.723 21.723 ↑ 1.0 1 7,241

Index Only Scan using kpissa_neip_key on kpissa (cost=0.00..0.27 rows=1 width=11) (actual time=0.002..0.003 rows=1 loops=7,241)

  • Index Cond: (neip = (sitemapping.neip)::text)
  • Heap Fetches: 0
32. 121.314 121.314 ↑ 1.0 1 40,438

Index Scan using kpissa_neip_key on kpissa (cost=0.00..0.39 rows=1 width=31) (actual time=0.002..0.003 rows=1 loops=40,438)

  • Index Cond: ((neip)::text = (public.omcmasterinfo.neip)::text)
  • Filter: ((ssaname)::text = ANY ('{select,GAYA,KHAMMAM,BALAGHAT,HAZARIBAGH,RANCHI,RAIPUR,MUNGER,BOKARO,BEHRAMPUR,CHANDRAPUR,ROBERTSGANJ,SAMBALPUR,ADILABAD,KHARAGPUR,VIZIANAGRAM,PURULIA,DALTANGANJ,DUMKA,CUTTACK,JAMSHEDPUR,KEONJHAR}'::text[]))
33. 0.000 0.000 ↓ 0.0 0 38,865

Index Scan using sitemaster_pkey on sitemaster (cost=0.00..0.28 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=38,865)

  • Index Cond: (sitemapping.siteid = id)