explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5afJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 101,346.028 ↑ 3,179.7 3,364 1

Finalize GroupAggregate (cost=20,831,391.94..22,474,582.06 rows=10,696,453 width=133) (actual time=98,637.843..101,346.028 rows=3,364 loops=1)

  • Group Key: "*SELECT* 4".month, arm.name, sc_user.last_name, sc_user.first_name, sc_user.middle_name
2. 2,795.214 101,555.907 ↑ 549.2 19,478 1

Gather Merge (cost=20,831,391.94..22,180,429.62 rows=10,696,452 width=101) (actual time=98,599.078..101,555.907 rows=19,478 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
3. 1,741.710 98,760.693 ↑ 640.6 2,783 7 / 7

Partial GroupAggregate (cost=20,830,391.84..20,879,417.25 rows=1,782,742 width=101) (actual time=96,359.319..98,760.693 rows=2,783 loops=7)

  • Group Key: "*SELECT* 4".month, arm.name, sc_user.last_name, sc_user.first_name, sc_user.middle_name
4. 18,634.464 97,018.983 ↓ 2.4 4,198,510 7 / 7

Sort (cost=20,830,391.84..20,834,848.70 rows=1,782,742 width=101) (actual time=96,347.731..97,018.983 rows=4,198,510 loops=7)

  • Sort Key: "*SELECT* 4".month DESC, arm.name, sc_user.last_name, sc_user.first_name, sc_user.middle_name
  • Sort Method: quicksort Memory: 741,869kB
  • Worker 0: Sort Method: quicksort Memory: 776,138kB
  • Worker 1: Sort Method: quicksort Memory: 750,837kB
  • Worker 2: Sort Method: quicksort Memory: 779,589kB
  • Worker 3: Sort Method: quicksort Memory: 645,944kB
  • Worker 4: Sort Method: quicksort Memory: 651,459kB
  • Worker 5: Sort Method: quicksort Memory: 680,621kB
5. 1,466.785 78,384.519 ↓ 2.4 4,198,510 7 / 7

Hash Join (cost=100.90..20,645,292.71 rows=1,782,742 width=101) (actual time=842.320..78,384.519 rows=4,198,510 loops=7)

  • Hash Cond: ("*SELECT* 4".arm_id = arm.id)
6. 1,695.816 76,917.041 ↓ 2.4 4,198,510 7 / 7

Hash Join (cost=71.08..20,640,560.67 rows=1,782,742 width=97) (actual time=841.493..76,917.041 rows=4,198,510 loops=7)

  • Hash Cond: ("*SELECT* 4".user_id = sc_user.id)
7. 471.411 75,219.440 ↑ 2.5 4,198,510 7 / 7

Parallel Append (cost=0.29..20,612,952.10 rows=10,467,789 width=56) (actual time=839.664..75,219.440 rows=4,198,510 loops=7)

8. 0.064 3.108 ↓ 1.0 3,695 1 / 7

Subquery Scan on *SELECT* 4 (cost=0.29..496.87 rows=3,683 width=56) (actual time=0.536..21.754 rows=3,695 loops=1)

9. 3.044 3.044 ↓ 1.0 3,695 1 / 7

Index Scan using idx_violation_report_operation_operation_date on violation_report_operation vro (cost=0.29..460.04 rows=3,683 width=56) (actual time=0.535..21.306 rows=3,695 loops=1)

  • Index Cond: ((operation_date >= '2018-08-23 14:40:00+03'::timestamp with time zone) AND (operation_date <= '2018-09-24 14:40:00+03'::timestamp with time zone))
  • Filter: ((arm_id IS NOT NULL) AND (sc_user_id <> '-1'::integer))
10. 588.325 74,074.606 ↑ 2.5 3,942,894 7 / 7

Subquery Scan on *SELECT* 2 (cost=0.57..20,227,482.76 rows=9,973,965 width=56) (actual time=1,911.656..74,074.606 rows=3,942,894 loops=7)

11. 73,486.281 73,486.281 ↓ 2.4 3,942,894 7 / 7

Parallel Index Scan using idx_rpi_operation_operation_date on rpi_operation ro (cost=0.57..20,127,743.11 rows=1,662,328 width=56) (actual time=1,911.653..73,486.281 rows=3,942,894 loops=7)

  • Index Cond: ((operation_date >= '2018-08-23 14:40:00+03'::timestamp with time zone) AND (operation_date <= '2018-09-24 14:40:00+03'::timestamp with time zone))
  • Filter: ((arm_id IS NOT NULL) AND (sc_user_id <> '-1'::integer))
  • Rows Removed by Filter: 6,384,188
12. 24.833 480.093 ↑ 1.1 362,680 4 / 7

Subquery Scan on *SELECT* 1 (cost=0.57..311,839.38 rows=383,362 width=56) (actual time=5.931..840.162 rows=362,680 loops=4)

13. 455.259 455.259 ↓ 4.7 362,680 4 / 7

Parallel Index Scan using idx_container_operation_operation_date on container_operation co (cost=0.57..308,005.76 rows=76,672 width=56) (actual time=5.928..796.704 rows=362,680 loops=4)

  • Index Cond: ((operation_date >= '2018-08-23 14:40:00+03'::timestamp with time zone) AND (operation_date <= '2018-09-24 14:40:00+03'::timestamp with time zone))
  • Filter: ((arm_id IS NOT NULL) AND (sc_user_id <> '-1'::integer))
  • Rows Removed by Filter: 340,842
14. 5.834 190.223 ↑ 3.0 111,632 3 / 7

Subquery Scan on *SELECT* 3 (cost=0.43..20,796.30 rows=335,443 width=56) (actual time=13.655..443.853 rows=111,632 loops=3)

15. 184.389 184.389 ↓ 1.0 111,632 3 / 7

Parallel Index Scan using idx_invoice_operation_operation_date on invoice_operation io (cost=0.43..17,441.87 rows=108,207 width=56) (actual time=13.653..430.240 rows=111,632 loops=3)

  • Index Cond: ((operation_date >= '2018-08-23 14:40:00+03'::timestamp with time zone) AND (operation_date <= '2018-09-24 14:40:00+03'::timestamp with time zone))
  • Filter: ((arm_id IS NOT NULL) AND (sc_user_id <> '-1'::integer))
  • Rows Removed by Filter: 1
16. 0.862 1.785 ↑ 1.0 1,946 7 / 7

Hash (cost=46.46..46.46 rows=1,946 width=57) (actual time=1.784..1.785 rows=1,946 loops=7)

  • Buckets: 2,048 Batches: 1 Memory Usage: 196kB
17. 0.923 0.923 ↑ 1.0 1,946 7 / 7

Seq Scan on sc_user (cost=0.00..46.46 rows=1,946 width=57) (actual time=0.184..0.923 rows=1,946 loops=7)

18. 0.266 0.693 ↑ 1.0 881 7 / 7

Hash (cost=18.81..18.81 rows=881 width=20) (actual time=0.692..0.693 rows=881 loops=7)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
19. 0.427 0.427 ↑ 1.0 881 7 / 7

Seq Scan on arm (cost=0.00..18.81 rows=881 width=20) (actual time=0.225..0.427 rows=881 loops=7)

Planning time : 1,295.951 ms
Execution time : 101,723.787 ms