explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jsyd

Settings
# exclusive inclusive rows x rows loops node
1. 27,886.812 528,448.091 ↓ 15.7 537,530 1

GroupAggregate (cost=7,370,228.84..7,372,533.56 rows=34,144 width=140) (actual time=458,463.654..528,448.091 rows=537,530 loops=1)

2. 268,135.691 500,561.279 ↓ 541.2 18,479,645 1

Sort (cost=7,370,228.84..7,370,314.20 rows=34,144 width=140) (actual time=458,463.565..500,561.279 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
3. 8,293.279 232,425.588 ↓ 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=197,950.806..232,425.588 rows=18,479,645 loops=1)

  • Hash Cond: (sitemapping.siteid = sitemaster.id)
4. 5,226.379 224,131.771 ↓ 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=197,950.250..224,131.771 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))
5. 7,089.289 218,206.856 ↓ 540.3 18,479,645 1

Merge Join (cost=7,160,410.41..7,363,334.84 rows=34,200 width=130) (actual time=197,943.519..218,206.856 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))
6. 21,338.186 69,011.530 ↑ 1.0 8,057,717 1

Sort (cost=2,733,625.15..2,753,845.20 rows=8,088,020 width=44) (actual time=65,014.352..69,011.530 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
7. 724.539 47,673.344 ↓ 1.0 8,120,432 1

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

8. 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))
9. 46,948.804 46,948.804 ↓ 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.013..46,948.804 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
10. 1,862.813 142,106.037 ↓ 2.7 18,483,976 1

Materialize (cost=4,426,785.26..4,460,612.65 rows=6,765,479 width=102) (actual time=132,929.154..142,106.037 rows=18,483,976 loops=1)

11. 47,112.975 140,243.224 ↑ 1.5 4,582,542 1

Sort (cost=4,426,785.26..4,443,698.95 rows=6,765,479 width=102) (actual time=132,929.149..140,243.224 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
12. 1,982.022 93,130.249 ↑ 1.5 4,582,542 1

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

  • Hash Cond: (public.bsckpierlangtable.omcid = public.omcmasterinfo.seq_no)
13. 1,051.720 91,147.921 ↑ 1.0 11,660,564 1

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

14. 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))
15. 90,096.200 90,096.200 ↑ 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.028..90,096.200 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
16. 0.052 0.306 ↑ 1.0 172 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
17. 0.080 0.254 ↑ 1.0 172 1

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

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

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

19. 0.033 0.144 ↑ 1.0 170 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.111 0.111 ↑ 1.0 170 1

Seq Scan on kpissa (cost=0.00..10.03 rows=170 width=31) (actual time=0.011..0.111 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
21. 694.893 698.536 ↓ 1,254.3 9,027,520 1

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

  • Sort Key: sitemapping.cellid, sitemapping.lacid, public.omcmasterinfo.seq_no
  • Sort Method: quicksort Memory: 947kB
22. 2.543 3.643 ↑ 1.0 6,902 1

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

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

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

24. 0.061 0.289 ↑ 1.1 281 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
25. 0.100 0.228 ↑ 1.1 281 1

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

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

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

27. 0.052 0.092 ↑ 1.0 296 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
28. 0.040 0.040 ↑ 1.0 296 1

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

29. 0.292 0.538 ↑ 1.0 1,937 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 79kB
30. 0.246 0.246 ↑ 1.0 1,937 1

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