explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1cZE

Settings
# exclusive inclusive rows x rows loops node
1. 76.062 28,000.184 ↓ 22,638.0 22,638 1

Sort (cost=168,683.77..168,683.77 rows=1 width=148) (actual time=27,997.741..28,000.184 rows=22,638 loops=1)

  • Sort Key: (date_trunc('hour'::text, (view_callsdroppedrate_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_callsdroppedrate_sdcch.cellid
  • Sort Method: quicksort Memory: 4533kB
2. 94.934 27,924.122 ↓ 22,638.0 22,638 1

HashAggregate (cost=168,683.73..168,683.76 rows=1 width=148) (actual time=27,905.237..27,924.122 rows=22,638 loops=1)

3. 68.738 27,829.188 ↓ 38,865.0 38,865 1

Nested Loop Left Join (cost=166,806.39..168,683.70 rows=1 width=148) (actual time=3,366.263..27,829.188 rows=38,865 loops=1)

4. 25.833 27,760.450 ↓ 38,865.0 38,865 1

Nested Loop (cost=166,806.39..168,683.41 rows=1 width=148) (actual time=3,366.253..27,760.450 rows=38,865 loops=1)

5. 2,540.449 27,613.303 ↓ 40,438.0 40,438 1

Nested Loop (cost=166,806.39..168,683.00 rows=1 width=128) (actual time=3,366.245..27,613.303 rows=40,438 loops=1)

  • Join Filter: (view_callsdroppedrate_sdcch.omcid = public.omcmasterinfo.seq_no)
  • Rows Removed by Join Filter: 21653507
6. 31.860 23,404.089 ↓ 72,555.0 72,555 1

Nested Loop Left Join (cost=166,806.39..168,671.28 rows=1 width=98) (actual time=3,366.230..23,404.089 rows=72,555 loops=1)

7. 87.099 3,709.824 ↓ 72,555.0 72,555 1

GroupAggregate (cost=166,806.39..168,310.81 rows=1 width=97) (actual time=3,365.952..3,709.824 rows=72,555 loops=1)

8. 195.128 3,622.725 ↓ 72,555.0 72,555 1

Merge Join (cost=166,806.39..168,310.77 rows=1 width=97) (actual time=3,365.940..3,622.725 rows=72,555 loops=1)

  • Merge Cond: ((view_callsdroppedrate_sdcch.rectimestamp = bsckpilaccelltable_view_temp.rectimestamp) AND (view_callsdroppedrate_sdcch.omcid = bsckpilaccelltable_view_temp.omcid) AND (view_callsdroppedrate_sdcch.lac = bsckpilaccelltable_view_temp.lac) AND (view_callsdroppedrate_sdcch.neinfo_id = bsckpilaccelltable_view_temp.neinfo_id) AND ((view_callsdroppedrate_sdcch.nename)::text = (bsckpilaccelltable_view_temp.nename)::text) AND (view_callsdroppedrate_sdcch.cellid = bsckpilaccelltable_view_temp.cellid) AND (view_callsdroppedrate_sdcch.trxid = bsckpilaccelltable_view_temp.trxid) AND (view_callsdroppedrate_sdcch.tchcodec = bsckpilaccelltable_view_temp.tchcodec))
9. 86.377 1,070.499 ↓ 2.8 72,555 1

Sort (cost=113,667.38..113,731.87 rows=25,794 width=57) (actual time=1,058.278..1,070.499 rows=72,555 loops=1)

  • Sort Key: view_callsdroppedrate_sdcch.rectimestamp, view_callsdroppedrate_sdcch.omcid, view_callsdroppedrate_sdcch.lac, view_callsdroppedrate_sdcch.neinfo_id, view_callsdroppedrate_sdcch.nename, view_callsdroppedrate_sdcch.cellid, view_callsdroppedrate_sdcch.trxid, view_callsdroppedrate_sdcch.tchcodec
  • Sort Method: quicksort Memory: 13276kB
10. 16.548 984.122 ↓ 2.8 72,555 1

Subquery Scan on view_callsdroppedrate_sdcch (cost=111,454.93..111,777.36 rows=25,794 width=57) (actual time=957.579..984.122 rows=72,555 loops=1)

11. 52.046 967.574 ↓ 2.8 72,555 1

Sort (cost=111,454.93..111,519.42 rows=25,794 width=65) (actual time=957.577..967.574 rows=72,555 loops=1)

  • Sort Key: public.bsckpierlangtable.rectimestamp
  • Sort Method: quicksort Memory: 13276kB
12. 302.734 915.528 ↓ 2.8 72,555 1

HashAggregate (cost=109,178.00..109,564.91 rows=25,794 width=65) (actual time=844.153..915.528 rows=72,555 loops=1)

13. 23.647 612.794 ↑ 1.0 256,235 1

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

14. 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))
15. 589.146 589.146 ↑ 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.036..589.146 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
16. 949.265 2,357.098 ↓ 7.0 287,810 1

Sort (cost=53,139.00..53,241.67 rows=41,067 width=81) (actual time=2,307.653..2,357.098 rows=287,810 loops=1)

  • Sort Key: bsckpilaccelltable_view_temp.rectimestamp, bsckpilaccelltable_view_temp.omcid, bsckpilaccelltable_view_temp.lac, bsckpilaccelltable_view_temp.neinfo_id, bsckpilaccelltable_view_temp.nename, bsckpilaccelltable_view_temp.cellid, bsckpilaccelltable_view_temp.trxid, bsckpilaccelltable_view_temp.tchcodec
  • Sort Method: quicksort Memory: 52763kB
17. 66.465 1,407.833 ↓ 7.0 287,816 1

Subquery Scan on bsckpilaccelltable_view_temp (cost=49,478.76..49,992.10 rows=41,067 width=81) (actual time=1,298.441..1,407.833 rows=287,816 loops=1)

18. 141.318 1,341.368 ↓ 7.0 287,816 1

Sort (cost=49,478.76..49,581.43 rows=41,067 width=97) (actual time=1,298.439..1,341.368 rows=287,816 loops=1)

  • Sort Key: public.bsckpilaccelltable.rectimestamp
  • Sort Method: quicksort Memory: 52763kB
19. 987.396 1,200.050 ↓ 7.0 287,816 1

HashAggregate (cost=45,407.86..46,331.86 rows=41,067 width=97) (actual time=833.944..1,200.050 rows=287,816 loops=1)

20. 40.300 212.654 ↑ 1.0 404,906 1

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

21. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on bsckpilaccelltable (cost=0.00..0.00 rows=1 width=128) (actual time=0.001..0.001 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))
22. 172.353 172.353 ↑ 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.023..172.353 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))
23. 58.073 19,662.405 ↓ 0.0 0 72,555

Nested Loop (cost=0.00..360.45 rows=1 width=50) (actual time=0.265..0.271 rows=0 loops=72,555)

  • Join Filter: ((public.omcmasterinfo.neip)::text = (public.kpissa.neip)::text)
24. 70.150 19,589.850 ↓ 0.0 0 72,555

Nested Loop (cost=0.00..360.17 rows=1 width=72) (actual time=0.265..0.270 rows=0 loops=72,555)

  • Join Filter: (view_callsdroppedrate_sdcch.omcid = public.omcmasterinfo.seq_no)
  • Rows Removed by Join Filter: 1
25. 18,574.080 18,574.080 ↑ 1.0 1 72,555

Index Scan using sitemapping_pkey on sitemapping (cost=0.00..357.64 rows=1 width=57) (actual time=0.198..0.256 rows=1 loops=72,555)

  • Index Cond: ((view_callsdroppedrate_sdcch.lac = lacid) AND (view_callsdroppedrate_sdcch.cellid = cellid))
26. 945.620 945.620 ↑ 1.0 1 72,740

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=72,740)

  • Index Cond: ((neip)::text = (sitemapping.neip)::text)
27. 14.482 14.482 ↑ 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.002 rows=1 loops=7,241)

  • Index Cond: (neip = (sitemapping.neip)::text)
  • Heap Fetches: 0
28. 1,668.765 1,668.765 ↑ 1.0 299 72,555

Seq Scan on omcmasterinfo (cost=0.00..7.99 rows=299 width=38) (actual time=0.002..0.023 rows=299 loops=72,555)

29. 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.003..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[]))
30. 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)