explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ds3b

Settings
# exclusive inclusive rows x rows loops node
1. 2,874.207 557,205.917 ↓ 15.7 537,530 1

Sort (cost=7,377,557.99..7,377,643.35 rows=34,144 width=140) (actual time=557,113.888..557,205.917 rows=537,530 loops=1)

  • Sort Key: (date_trunc('hour'::text, (public.bsckpierlangtable.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, public.bsckpierlangtable.cellid
  • Sort Method: external sort Disk: 65640kB
2. 28,273.974 554,331.710 ↓ 15.7 537,530 1

GroupAggregate (cost=7,370,228.84..7,372,533.56 rows=34,144 width=140) (actual time=483,162.529..554,331.710 rows=537,530 loops=1)

3. 286,278.048 526,057.736 ↓ 541.2 18,479,645 1

Sort (cost=7,370,228.84..7,370,314.20 rows=34,144 width=140) (actual time=483,162.436..526,057.736 rows=18,479,645 loops=1)

  • Sort Key: (date_trunc('hour'::text, (public.bsckpierlangtable.rectimestamp + '05:30:00'::interval))), public.kpissa.ssaname, public.kpissa.omcip, public.kpissa.neip, public.omcmasterinfo.nename, sitemapping.sitename, sitemaster.sitenumber, public.bsckpierlangtable.cellid, public.bsckpierlangtable.lac, sitemapping.cellname
  • Sort Method: external merge Disk: 2684192kB
4. 8,360.123 239,779.688 ↓ 541.2 18,479,645 1

Hash Left Join (cost=7,161,268.61..7,365,204.41 rows=34,144 width=140) (actual time=204,556.546..239,779.688 rows=18,479,645 loops=1)

  • Hash Cond: (sitemapping.siteid = sitemaster.id)
5. 5,367.322 231,410.693 ↓ 541.2 18,479,645 1

Merge Left Join (cost=7,161,209.03..7,364,461.94 rows=34,144 width=140) (actual time=204,547.640..231,410.693 rows=18,479,645 loops=1)

  • Merge Cond: ((public.bsckpilaccelltable.cellid = sitemapping.cellid) AND (public.bsckpilaccelltable.lac = sitemapping.lacid) AND (public.bsckpilaccelltable.omcid = public.omcmasterinfo.seq_no))
6. 7,197.206 225,308.628 ↓ 540.3 18,479,645 1

Merge Join (cost=7,160,410.41..7,363,334.84 rows=34,200 width=130) (actual time=204,520.902..225,308.628 rows=18,479,645 loops=1)

  • Merge Cond: ((public.bsckpilaccelltable.cellid = public.bsckpierlangtable.cellid) AND (public.bsckpilaccelltable.lac = public.bsckpierlangtable.lac) AND (public.bsckpilaccelltable.omcid = public.bsckpierlangtable.omcid) AND (public.bsckpilaccelltable.rectimestamp = public.bsckpierlangtable.rectimestamp))
7. 24,084.469 73,411.657 ↑ 1.0 8,057,717 1

Sort (cost=2,733,625.15..2,753,845.20 rows=8,088,020 width=44) (actual time=69,208.732..73,411.657 rows=8,057,717 loops=1)

  • Sort Key: public.bsckpilaccelltable.cellid, public.bsckpilaccelltable.lac, public.bsckpilaccelltable.omcid, public.bsckpilaccelltable.rectimestamp
  • Sort Method: external merge Disk: 460424kB
8. 750.299 49,327.188 ↓ 1.0 8,120,432 1

Append (cost=0.00..810,413.81 rows=8,088,020 width=44) (actual time=0.017..49,327.188 rows=8,120,432 loops=1)

9. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: ((cellid >= 0::bigint) AND (lac >= 0::bigint) AND (rectimestamp >= '2018-04-30 18:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-29 18:29:59.999'::timestamp without time zone) AND (chantype = 1))
10. 48,576.889 48,576.889 ↓ 1.0 8,120,432 1

Seq Scan on bsckpilaccelltable_052018 bsckpilaccelltable (cost=0.00..810,413.81 rows=8,088,019 width=44) (actual time=0.015..48,576.889 rows=8,120,432 loops=1)

  • Filter: ((cellid >= 0::bigint) AND (lac >= 0::bigint) AND (rectimestamp >= '2018-04-30 18:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-29 18:29:59.999'::timestamp without time zone) AND (chantype = 1))
  • Rows Removed by Filter: 4509381
11. 1,911.257 144,699.765 ↓ 2.7 18,483,976 1

Materialize (cost=4,426,785.26..4,460,612.65 rows=6,765,479 width=102) (actual time=135,312.158..144,699.765 rows=18,483,976 loops=1)

12. 44,781.862 142,788.508 ↑ 1.5 4,582,542 1

Sort (cost=4,426,785.26..4,443,698.95 rows=6,765,479 width=102) (actual time=135,312.146..142,788.508 rows=4,582,542 loops=1)

  • Sort Key: public.bsckpierlangtable.cellid, public.bsckpierlangtable.lac, public.omcmasterinfo.seq_no, public.bsckpierlangtable.rectimestamp
  • Sort Method: external merge Disk: 529384kB
13. 2,045.347 98,006.646 ↑ 1.5 4,582,542 1

Hash Join (cost=25.14..2,179,295.76 rows=6,765,479 width=102) (actual time=43.178..98,006.646 rows=4,582,542 loops=1)

  • Hash Cond: (public.bsckpierlangtable.omcid = public.omcmasterinfo.seq_no)
14. 1,054.261 95,939.379 ↑ 1.0 11,660,564 1

Append (cost=0.00..2,067,659.89 rows=11,721,585 width=44) (actual time=0.031..95,939.379 rows=11,660,564 loops=1)

15. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((cellid >= 0::bigint) AND (lac >= 0::bigint) AND (rectimestamp >= '2018-04-30 18:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-29 18:29:59.999'::timestamp without time zone) AND (chantype = 1))
16. 94,885.116 94,885.116 ↑ 1.0 11,660,564 1

Seq Scan on bsckpierlangtable_052018 bsckpierlangtable (cost=0.00..2,067,659.89 rows=11,721,584 width=44) (actual time=0.029..94,885.116 rows=11,660,564 loops=1)

  • Filter: ((cellid >= 0::bigint) AND (lac >= 0::bigint) AND (rectimestamp >= '2018-04-30 18:30:00'::timestamp without time zone) AND (rectimestamp <= '2018-05-29 18:29:59.999'::timestamp without time zone) AND (chantype = 1))
  • Rows Removed by Filter: 35571519
17. 0.077 21.920 ↑ 1.0 172 1

Hash (cost=22.99..22.99 rows=172 width=58) (actual time=21.920..21.920 rows=172 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
18. 0.124 21.843 ↑ 1.0 172 1

Hash Join (cost=12.16..22.99 rows=172 width=58) (actual time=21.486..21.843 rows=172 loops=1)

  • Hash Cond: ((public.omcmasterinfo.neip)::text = (public.kpissa.neip)::text)
19. 10.582 10.582 ↑ 1.0 299 1

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

20. 0.048 11.137 ↑ 1.0 170 1

Hash (cost=10.03..10.03 rows=170 width=31) (actual time=11.137..11.137 rows=170 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 11.089 11.089 ↑ 1.0 170 1

Seq Scan on kpissa (cost=0.00..10.03 rows=170 width=31) (actual time=0.014..11.089 rows=170 loops=1)

  • Filter: ((ssaname)::text = ANY ('{select,GAYA,KHAMMAM,BALAGHAT,HAZARIBAGH,RANCHI,RAIPUR,MUNGER,BOKARO,BEHRAMPUR,CHANDRAPUR}'::text[]))
  • Rows Removed by Filter: 126
22. 711.269 734.743 ↓ 1,254.3 9,027,520 1

Sort (cost=798.62..816.61 rows=7,197 width=50) (actual time=26.733..734.743 rows=9,027,520 loops=1)

  • Sort Key: sitemapping.cellid, sitemapping.lacid, public.omcmasterinfo.seq_no
  • Sort Method: quicksort Memory: 947kB
23. 2.550 23.474 ↑ 1.0 6,902 1

Hash Join (cost=25.50..337.54 rows=7,197 width=50) (actual time=0.324..23.474 rows=6,902 loops=1)

  • Hash Cond: ((sitemapping.neip)::text = (public.omcmasterinfo.neip)::text)
24. 20.633 20.633 ↑ 1.0 7,738 1

Seq Scan on sitemapping (cost=0.00..201.38 rows=7,738 width=57) (actual time=0.010..20.633 rows=7,738 loops=1)

25. 0.062 0.291 ↑ 1.1 281 1

Hash (cost=21.76..21.76 rows=299 width=26) (actual time=0.291..0.291 rows=281 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
26. 0.102 0.229 ↑ 1.1 281 1

Hash Join (cost=9.66..21.76 rows=299 width=26) (actual time=0.114..0.229 rows=281 loops=1)

  • Hash Cond: ((public.omcmasterinfo.neip)::text = (public.kpissa.neip)::text)
27. 0.032 0.032 ↑ 1.0 299 1

Seq Scan on omcmasterinfo (cost=0.00..7.99 rows=299 width=15) (actual time=0.003..0.032 rows=299 loops=1)

28. 0.064 0.095 ↑ 1.0 296 1

Hash (cost=5.96..5.96 rows=296 width=11) (actual time=0.095..0.095 rows=296 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
29. 0.031 0.031 ↑ 1.0 296 1

Seq Scan on kpissa (cost=0.00..5.96 rows=296 width=11) (actual time=0.003..0.031 rows=296 loops=1)

30. 0.307 8.872 ↑ 1.0 1,937 1

Hash (cost=35.37..35.37 rows=1,937 width=8) (actual time=8.872..8.872 rows=1,937 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
31. 8.565 8.565 ↑ 1.0 1,937 1

Seq Scan on sitemaster (cost=0.00..35.37 rows=1,937 width=8) (actual time=0.006..8.565 rows=1,937 loops=1)