explain.depesz.com

PostgreSQL's explain analyze made readable

Result: komW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 24,643.831 ↑ 1.0 1 1

Subquery Scan on thedata (cost=6,455,746.47..6,455,746.52 rows=1 width=32) (actual time=24,643.830..24,643.831 rows=1 loops=1)

2. 0.019 24,643.801 ↑ 1.0 1 1

Group (cost=6,455,746.47..6,455,746.48 rows=1 width=128) (actual time=24,643.801..24,643.801 rows=1 loops=1)

  • Group Key: (array_agg((to_char((generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval)), 'dd'::text)))), (array_agg((count(kj.*))))
3. 0.011 24,643.782 ↑ 1.0 1 1

Sort (cost=6,455,746.47..6,455,746.47 rows=1 width=64) (actual time=24,643.782..24,643.782 rows=1 loops=1)

  • Sort Key: (array_agg((to_char((generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval)), 'dd'::text)))), (array_agg((count(kj.*))))
  • Sort Method: quicksort Memory: 25kB
4. 0.022 24,643.771 ↑ 1.0 1 1

Aggregate (cost=6,455,746.44..6,455,746.45 rows=1 width=64) (actual time=24,643.771..24,643.771 rows=1 loops=1)

5. 17.164 24,643.749 ↑ 14.3 14 1

GroupAggregate (cost=6,260,058.05..6,455,743.43 rows=200 width=48) (actual time=24,550.784..24,643.749 rows=14 loops=1)

  • Group Key: (generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval))
6. 38.234 24,626.585 ↑ 43.2 196,741 1

Merge Join (cost=6,260,058.05..6,413,202.80 rows=8,507,626 width=94) (actual time=24,543.713..24,626.585 rows=196,741 loops=1)

  • Merge Cond: ((generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval)) = (date_trunc('day'::text, kj.reportdate)))
7. 0.014 0.029 ↑ 66.7 15 1

Sort (cost=64.86..67.36 rows=1,000 width=8) (actual time=0.024..0.029 rows=15 loops=1)

  • Sort Key: (generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval))
  • Sort Method: quicksort Memory: 25kB
8. 0.015 0.015 ↑ 66.7 15 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.011..0.015 rows=15 loops=1)

9. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

10. 18.248 24,588.322 ↑ 8.6 196,742 1

Materialize (cost=6,259,993.19..6,268,500.82 rows=1,701,525 width=94) (actual time=24,543.680..24,588.322 rows=196,742 loops=1)

11. 162.114 24,570.074 ↑ 8.6 196,742 1

Sort (cost=6,259,993.19..6,264,247.01 rows=1,701,525 width=94) (actual time=24,543.677..24,570.074 rows=196,742 loops=1)

  • Sort Key: (date_trunc('day'::text, kj.reportdate))
  • Sort Method: external merge Disk: 23,504kB
12. 813.030 24,407.960 ↑ 8.6 196,812 1

Hash Join (cost=5,568,649.33..5,996,661.49 rows=1,701,525 width=94) (actual time=18,804.750..24,407.960 rows=196,812 loops=1)

  • Hash Cond: (ph.idpemilik = u.id)
13. 1,714.563 20,926.763 ↑ 8.7 196,828 1

Hash Join (cost=5,237,368.33..5,577,707.05 rows=1,713,692 width=102) (actual time=16,036.798..20,926.763 rows=196,828 loops=1)

  • Hash Cond: (ph.idhewan = h.id)
14. 1,173.128 13,496.488 ↑ 8.7 196,859 1

Merge Join (cost=4,555,243.92..4,759,824.74 rows=1,720,344 width=118) (actual time=10,314.717..13,496.488 rows=196,859 loops=1)

  • Merge Cond: (ph.idhewan = kj.idhewan)
  • Join Filter: (((ph.tanggalakhir IS NULL) OR (ph.tanggalakhir > kj.reportdate)) AND (ph.tanggalmulai <= kj.reportdate))
  • Rows Removed by Join Filter: 1,232
15. 8,724.602 11,954.997 ↓ 1.0 17,889,979 1

Sort (cost=2,968,603.87..3,012,519.34 rows=17,566,187 width=32) (actual time=9,994.703..11,954.997 rows=17,889,979 loops=1)

  • Sort Key: ph.idhewan
  • Sort Method: external merge Disk: 599,528kB
16. 3,230.395 3,230.395 ↓ 1.0 17,889,985 1

Seq Scan on pemilik_hewan ph (cost=0.00..434,551.44 rows=17,566,187 width=32) (actual time=0.019..3,230.395 rows=17,889,985 loops=1)

  • Filter: (NOT del)
  • Rows Removed by Filter: 3,351
17. 18.632 368.363 ↑ 24.6 198,091 1

Materialize (cost=1,586,626.93..1,611,012.37 rows=4,877,088 width=102) (actual time=319.883..368.363 rows=198,091 loops=1)

18. 188.512 349.731 ↑ 24.7 197,144 1

Sort (cost=1,586,626.93..1,598,819.65 rows=4,877,088 width=102) (actual time=319.879..349.731 rows=197,144 loops=1)

  • Sort Key: kj.idhewan
  • Sort Method: external merge Disk: 23,536kB
19. 122.861 161.219 ↑ 24.7 197,144 1

Bitmap Heap Scan on kejadian kj (cost=241,015.27..778,123.75 rows=4,877,088 width=102) (actual time=39.373..161.219 rows=197,144 loops=1)

  • Recheck Cond: (((reportdate)::date >= (now() - '14 days'::interval)) AND (idtipekejadian = 2) AND (NOT del))
  • Heap Blocks: exact=7,499
20. 38.358 38.358 ↑ 22.6 216,256 1

Bitmap Index Scan on kejadian_tipe_reportdate_del (cost=0.00..239,796.00 rows=4,877,088 width=0) (actual time=38.358..38.358 rows=216,256 loops=1)

  • Index Cond: (((reportdate)::date >= (now() - '14 days'::interval)) AND (idtipekejadian = 2))
21. 2,422.112 5,715.712 ↑ 1.0 17,777,030 1

Hash (cost=384,935.37..384,935.37 rows=18,114,323 width=4) (actual time=5,715.712..5,715.712 rows=17,777,030 loops=1)

  • Buckets: 524,288 Batches: 64 Memory Usage: 13,889kB
22. 3,293.600 3,293.600 ↑ 1.0 17,777,030 1

Seq Scan on hewan h (cost=0.00..384,935.37 rows=18,114,323 width=4) (actual time=0.016..3,293.600 rows=17,777,030 loops=1)

  • Filter: (NOT del)
  • Rows Removed by Filter: 77,118
23. 1,019.026 2,668.167 ↑ 1.0 7,561,271 1

Hash (cost=206,875.13..206,875.13 rows=7,582,790 width=4) (actual time=2,668.167..2,668.167 rows=7,561,271 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 12,416kB
24. 1,649.141 1,649.141 ↑ 1.0 7,561,271 1

Seq Scan on users u (cost=0.00..206,875.13 rows=7,582,790 width=4) (actual time=0.009..1,649.141 rows=7,561,271 loops=1)

  • Filter: (NOT del)
  • Rows Removed by Filter: 61,763
Planning time : 1.211 ms
Execution time : 24,733.498 ms