explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gTR6u : Optimization for: plan #komW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.028 32,798.209 ↑ 1.0 1 1

Subquery Scan on thedata (cost=1,872,764.77..1,872,764.82 rows=1 width=32) (actual time=32,798.205..32,798.209 rows=1 loops=1)

  • JIT:
  • Functions: 206
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 33.766 ms, Inlining 784.643 ms, Optimization 2763.624 ms, Emission 1533.791 ms, Total 5115.824 ms
2. 0.023 32,798.181 ↑ 1.0 1 1

Group (cost=1,872,764.77..1,872,764.78 rows=1 width=128) (actual time=32,798.178..32,798.181 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.010 32,798.158 ↑ 1.0 1 1

Sort (cost=1,872,764.77..1,872,764.77 rows=1 width=64) (actual time=32,798.157..32,798.158 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.063 32,798.148 ↑ 1.0 1 1

Aggregate (cost=1,872,764.74..1,872,764.75 rows=1 width=64) (actual time=32,798.146..32,798.148 rows=1 loops=1)

5. 133.239 32,798.085 ↑ 15.4 13 1

GroupAggregate (cost=1,696,333.16..1,872,761.73 rows=200 width=48) (actual time=32,293.165..32,798.085 rows=13 loops=1)

  • Group Key: (generate_series(date_trunc('day'::text, now()), (date_trunc('day'::text, now()) - '14 days'::interval), '-1 days'::interval))
6. 294.402 32,664.846 ↑ 45.0 174,265 1

Merge Join (cost=1,696,333.16..1,833,555.10 rows=7,840,826 width=94) (actual time=32,198.454..32,664.846 rows=174,265 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.042 663.375 ↑ 66.7 15 1

Sort (cost=64.86..67.36 rows=1,000 width=8) (actual time=663.353..663.375 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.037 663.333 ↑ 66.7 15 1

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

9. 663.296 663.296 ↑ 1.0 1 1

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

10. 423.311 31,707.069 ↑ 9.0 174,266 1

Sort (cost=1,696,268.30..1,700,188.72 rows=1,568,165 width=94) (actual time=31,535.070..31,707.069 rows=174,266 loops=1)

  • Sort Key: (date_trunc('day'::text, kj.reportdate))
  • Sort Method: quicksort Memory: 30,661kB
11. 33.333 31,283.758 ↑ 9.0 174,336 1

Gather (cost=1,116,791.54..1,534,899.06 rows=1,568,165 width=94) (actual time=20,468.618..31,283.758 rows=174,336 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
12. 5,510.876 31,250.425 ↑ 11.2 34,867 5 / 5

Parallel Hash Join (cost=1,115,791.54..1,377,082.56 rows=392,041 width=94) (actual time=20,424.370..31,250.425 rows=34,867 loops=5)

  • Hash Cond: (h.id = ph.idhewan)
13. 5,326.007 5,326.007 ↑ 1.3 3,550,108 5 / 5

Parallel Seq Scan on hewan h (cost=0.00..243,668.08 rows=4,438,091 width=4) (actual time=0.026..5,326.007 rows=3,550,108 loops=5)

  • Filter: (NOT del)
  • Rows Removed by Filter: 15,412
14. 92.252 20,413.542 ↑ 11.3 34,873 5 / 5

Parallel Hash (cost=1,110,859.43..1,110,859.43 rows=394,569 width=110) (actual time=20,413.541..20,413.542 rows=34,873 loops=5)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 42,400kB
15. 169.976 20,321.290 ↑ 11.3 34,873 5 / 5

Parallel Hash Join (cost=805,200.15..1,110,859.43 rows=394,569 width=110) (actual time=9,223.716..20,321.290 rows=34,873 loops=5)

  • Hash Cond: (ph.idpemilik = u.id)
16. 5,652.250 14,210.826 ↑ 11.4 34,875 5 / 5

Parallel Hash Join (cost=632,512.30..937,127.72 rows=397,658 width=118) (actual time=3,269.760..14,210.826 rows=34,875 loops=5)

  • Hash 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: 219
17. 5,304.585 5,304.585 ↑ 1.2 3,572,678 5 / 5

Parallel Seq Scan on pemilik_hewan ph (cost=0.00..283,312.09 rows=4,464,922 width=32) (actual time=0.031..5,304.585 rows=3,572,678 loops=5)

  • Filter: (NOT del)
  • Rows Removed by Filter: 670
18. 155.492 3,253.991 ↑ 34.8 34,925 5 / 5

Parallel Hash (cost=617,306.61..617,306.61 rows=1,216,455 width=102) (actual time=3,253.990..3,253.991 rows=34,925 loops=5)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 90,240kB
19. 3,098.499 3,098.499 ↑ 34.8 34,925 5 / 5

Parallel Seq Scan on kejadian kj (cost=0.00..617,306.61 rows=1,216,455 width=102) (actual time=1,528.635..3,098.499 rows=34,925 loops=5)

  • Filter: ((NOT del) AND (idtipekejadian = 2) AND ((reportdate)::date >= (now() - '14 days'::interval)))
  • Rows Removed by Filter: 6,816,420
20. 2,744.624 5,940.488 ↑ 1.3 1,510,764 5 / 5

Parallel Hash (cost=149,076.74..149,076.74 rows=1,888,889 width=4) (actual time=5,940.486..5,940.488 rows=1,510,764 loops=5)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 361,120kB
21. 3,195.864 3,195.864 ↑ 1.3 1,510,764 5 / 5

Parallel Seq Scan on users u (cost=0.00..149,076.74 rows=1,888,889 width=4) (actual time=884.570..3,195.864 rows=1,510,764 loops=5)

  • Filter: (NOT del)
  • Rows Removed by Filter: 12,346
Planning time : 1.393 ms
Execution time : 32,861.224 ms