explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Fh9

Settings
# exclusive inclusive rows x rows loops node
1. 34,974.526 222,292.918 ↓ 15.7 537,530 1

HashAggregate (cost=4,874,313.75..4,875,338.07 rows=34,144 width=140) (actual time=221,395.298..222,292.918 rows=537,530 loops=1)

2. 8,145.809 187,318.392 ↓ 541.2 18,479,645 1

Hash Left Join (cost=4,686,096.61..4,873,118.71 rows=34,144 width=140) (actual time=162,722.069..187,318.392 rows=18,479,645 loops=1)

  • Hash Cond: (sitemapping.siteid = sitemaster.id)
3. 5,589.689 179,172.007 ↓ 541.2 18,479,645 1

Merge Left Join (cost=4,686,037.03..4,872,376.25 rows=34,144 width=140) (actual time=162,721.467..179,172.007 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))
4. 7,343.212 172,906.256 ↓ 540.3 18,479,645 1

Merge Join (cost=4,685,238.41..4,871,249.14 rows=34,200 width=130) (actual time=162,714.537..172,906.256 rows=18,479,645 loops=1)

  • Merge Cond: ((public.bsckpierlangtable.cellid = public.bsckpilaccelltable.cellid) AND (public.bsckpierlangtable.lac = public.bsckpilaccelltable.lac) AND (public.bsckpierlangtable.omcid = public.bsckpilaccelltable.omcid) AND (public.bsckpierlangtable.rectimestamp = public.bsckpilaccelltable.rectimestamp))
5. 18,410.746 106,943.508 ↑ 1.5 4,582,542 1

Sort (cost=2,946,831.26..2,963,744.95 rows=6,765,479 width=102) (actual time=106,303.454..106,943.508 rows=4,582,542 loops=1)

  • Sort Key: public.bsckpierlangtable.cellid, public.bsckpierlangtable.lac, public.omcmasterinfo.seq_no, public.bsckpierlangtable.rectimestamp
  • Sort Method: quicksort Memory: 853699kB
6. 1,964.980 88,532.762 ↑ 1.5 4,582,542 1

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

  • Hash Cond: (public.bsckpierlangtable.omcid = public.omcmasterinfo.seq_no)
7. 1,090.172 86,567.452 ↑ 1.0 11,660,564 1

Append (cost=0.00..2,067,659.89 rows=11,721,585 width=44) (actual time=0.018..86,567.452 rows=11,660,564 loops=1)

8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on bsckpierlangtable (cost=0.00..0.00 rows=1 width=44) (actual time=0.001..0.001 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))
9. 85,477.279 85,477.279 ↑ 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.016..85,477.279 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
10. 0.042 0.330 ↑ 1.0 172 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
11. 0.088 0.288 ↑ 1.0 172 1

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

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

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

13. 0.051 0.151 ↑ 1.0 170 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.100 0.100 ↑ 1.0 170 1

Seq Scan on kpissa (cost=0.00..10.03 rows=170 width=31) (actual time=0.005..0.100 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
15. 11,179.833 58,619.536 ↓ 2.9 23,639,057 1

Sort (cost=1,738,407.15..1,758,627.20 rows=8,088,020 width=44) (actual time=56,411.071..58,619.536 rows=23,639,057 loops=1)

  • Sort Key: public.bsckpilaccelltable.cellid, public.bsckpilaccelltable.lac, public.bsckpilaccelltable.omcid, public.bsckpilaccelltable.rectimestamp
  • Sort Method: quicksort Memory: 831017kB
16. 737.176 47,439.703 ↓ 1.0 8,120,432 1

Append (cost=0.00..810,413.81 rows=8,088,020 width=44) (actual time=0.028..47,439.703 rows=8,120,432 loops=1)

17. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on bsckpilaccelltable (cost=0.00..0.00 rows=1 width=44) (actual time=0.001..0.001 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))
18. 46,702.526 46,702.526 ↓ 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.026..46,702.526 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
19. 672.235 676.062 ↓ 1,254.3 9,027,520 1

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

  • Sort Key: sitemapping.cellid, sitemapping.lacid, public.omcmasterinfo.seq_no
  • Sort Method: quicksort Memory: 947kB
20. 2.517 3.827 ↑ 1.0 6,902 1

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

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

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

22. 0.045 0.276 ↑ 1.1 281 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
23. 0.106 0.231 ↑ 1.1 281 1

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

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

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

25. 0.029 0.087 ↑ 1.0 296 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
26. 0.058 0.058 ↑ 1.0 296 1

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

27. 0.299 0.576 ↑ 1.0 1,937 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
28. 0.277 0.277 ↑ 1.0 1,937 1

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