explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WcF1

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,508.996 ↑ 28.6 7 1

Subquery Scan on PoolTotals12 (cost=256,261.03..256,625.09 rows=200 width=118) (actual time=4,504.319..4,508.996 rows=7 loops=1)

2. 2.513 4,508.994 ↑ 28.6 7 1

GroupAggregate (cost=256,261.03..256,623.09 rows=200 width=118) (actual time=4,504.318..4,508.994 rows=7 loops=1)

  • Group Key: "X"."countyNo", "X"."juryYear", "X"."poolId", "X"."beginDate", "X"."endDate", "X"."jurorsInPool", "X"."jurorsRequested
3. 5.197 4,504.038 ↓ 10.2 7,514 1

Sort (cost=256,261.03..256,262.87 rows=736 width=118) (actual time=4,503.612..4,504.038 rows=7,514 loops=1)

  • Sort Key: "X"."juryYear", "X"."poolId", "X"."beginDate", "X"."endDate", "X"."jurorsInPool", "X"."jurorsRequested
  • Sort Method: quicksort Memory: 1249kB
4. 0.667 4,498.841 ↓ 10.2 7,514 1

Subquery Scan on X (cost=256,211.27..256,225.99 rows=736 width=118) (actual time=4,496.250..4,498.841 rows=7,514 loops=1)

5. 9.121 4,498.174 ↓ 10.2 7,514 1

HashAggregate (cost=256,211.27..256,218.63 rows=736 width=122) (actual time=4,496.250..4,498.174 rows=7,514 loops=1)

  • Group Key: "P"."poolId", "P"."juryYear", "P"."countyNo", "P"."beginDate", "P"."endDate", (sum(CASE WHEN ("J_1"."jurorId" IS NOT NULL) THEN 1 ELSE 0 END)), "P".requested, "A"."jurorId", "A"."actionCode
6. 0.926 4,489.053 ↓ 11.6 8,539 1

Append (cost=6,407.62..256,194.71 rows=736 width=122) (actual time=146.021..4,489.053 rows=8,539 loops=1)

7. 4.157 4,391.519 ↓ 15.1 5,571 1

Hash Anti Join (cost=6,407.62..249,565.19 rows=368 width=46) (actual time=146.021..4,391.519 rows=5,571 loops=1)

  • Hash Cond: ((("A"."countyNo")::smallint = (a2."countyNo")::smallint) AND (("A"."jurorId")::integer = (a2."jurorId")::integer) AND (("A"."juryYear")::text = (a2."juryYear")::text))
  • Join Filter: ((a2."startDate")::date >= ("A"."startDate")::date)
  • Rows Removed by Join Filter: 156
8. 378.620 4,271.248 ↓ 14.1 5,572 1

Hash Right Join (cost=947.94..244,072.10 rows=395 width=57) (actual time=29.015..4,271.248 rows=5,572 loops=1)

  • Hash Cond: ((("A"."countyNo")::smallint = ("J"."countyNo")::smallint) AND (("A"."jurorId")::integer = ("J"."jurorId")::integer) AND (("A"."juryYear")::text = ("J"."juryYear")::text))
9. 3,883.032 3,883.032 ↑ 1.0 2,226,878 1

Seq Scan on "Action" "A" (cost=0.00..218,066.97 rows=2,227,266 width=20) (actual time=4.481..3,883.032 rows=2,226,878 loops=1)

  • Filter: ((("countyNo")::smallint = '40'::smallint) AND (("actionCode")::text = ANY ('{QSENT,QRET,QRETU}'::text[])))
  • Rows Removed by Filter: 10068964
10. 0.798 9.596 ↓ 7.5 2,969 1

Hash (cost=941.03..941.03 rows=395 width=48) (actual time=9.596..9.596 rows=2,969 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 288kB
11. 0.315 8.798 ↓ 7.5 2,969 1

Nested Loop Left Join (cost=492.45..941.03 rows=395 width=48) (actual time=6.520..8.798 rows=2,969 loops=1)

12. 0.795 6.502 ↑ 35.1 7 1

HashAggregate (cost=492.02..494.48 rows=246 width=37) (actual time=6.497..6.502 rows=7 loops=1)

  • Group Key: "P"."poolId", "P"."juryYear", "P"."countyNo
13. 0.334 5.707 ↑ 1.4 2,969 1

Nested Loop Left Join (cost=0.71..450.92 rows=4,110 width=33) (actual time=0.089..5.707 rows=2,969 loops=1)

14. 0.158 0.158 ↑ 35.1 7 1

Index Scan using "Pool_end" on "Pool" "P" (cost=0.28..6.83 rows=246 width=29) (actual time=0.047..0.158 rows=7 loops=1)

  • Index Cond: (("endDate")::date >= '2019-08-07'::date)
  • Filter: ((("beginDate")::date <= '2019-08-14'::date) AND ('40'::smallint = ("countyNo")::smallint))
  • Rows Removed by Filter: 263
15. 5.215 5.215 ↓ 30.3 424 7

Index Scan using "Juror_PoolPanel" on "Juror" "J_1" (cost=0.43..1.67 rows=14 width=21) (actual time=0.022..0.745 rows=424 loops=7)

  • Index Cond: ((("P"."juryYear")::text = ("juryYear")::text) AND (("P"."poolId")::text = ("poolId")::text) AND (("P"."countyNo")::smallint = ("countyNo")::smallint) AND (("countyNo")::smallint = '40'::smallint))
16. 1.981 1.981 ↓ 30.3 424 7

Index Scan using "Juror_PoolPanel" on "Juror" "J" (cost=0.43..1.67 rows=14 width=21) (actual time=0.016..0.283 rows=424 loops=7)

  • Index Cond: ((("P"."juryYear")::text = ("juryYear")::text) AND (("P"."poolId")::text = ("poolId")::text) AND (("P"."countyNo")::smallint = ("countyNo")::smallint) AND (("countyNo")::smallint = '40'::smallint))
17. 32.432 116.114 ↑ 1.0 153,615 1

Hash (cost=2,768.37..2,768.37 rows=153,789 width=15) (actual time=116.114..116.114 rows=153,615 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9249kB
18. 83.682 83.682 ↑ 1.0 153,615 1

Index Only Scan using "Action_MFROM" on "Action" a2 (cost=0.42..2,768.37 rows=153,789 width=15) (actual time=14.466..83.682 rows=153,615 loops=1)

  • Index Cond: ("countyNo" = '40'::smallint)
  • Heap Fetches: 7
19. 2.040 96.608 ↓ 8.1 2,968 1

Hash Anti Join (cost=5,952.56..6,622.15 rows=368 width=46) (actual time=76.043..96.608 rows=2,968 loops=1)

  • Hash Cond: ((("A_1"."countyNo")::smallint = (a2_1."countyNo")::smallint) AND (("A_1"."jurorId")::integer = (a2_1."jurorId")::integer) AND (("A_1"."juryYear")::text = (a2_1."juryYear")::text))
  • Join Filter: ((a2_1."startDate")::date >= ("A_1"."startDate")::date)
  • Rows Removed by Join Filter: 32
20. 1.083 23.276 ↓ 7.5 2,969 1

Nested Loop Left Join (cost=492.88..1,129.06 rows=395 width=57) (actual time=3.891..23.276 rows=2,969 loops=1)

21. 0.406 7.348 ↓ 7.5 2,969 1

Nested Loop Left Join (cost=492.45..941.03 rows=395 width=58) (actual time=3.854..7.348 rows=2,969 loops=1)

22. 0.754 3.841 ↑ 35.1 7 1

HashAggregate (cost=492.02..494.48 rows=246 width=37) (actual time=3.832..3.841 rows=7 loops=1)

  • Group Key: "P_1"."poolId", "P_1"."juryYear", "P_1"."countyNo
23. 0.297 3.087 ↑ 1.4 2,969 1

Nested Loop Left Join (cost=0.71..450.92 rows=4,110 width=33) (actual time=0.042..3.087 rows=2,969 loops=1)

24. 0.095 0.095 ↑ 35.1 7 1

Index Scan using "Pool_end" on "Pool" "P_1" (cost=0.28..6.83 rows=246 width=29) (actual time=0.017..0.095 rows=7 loops=1)

  • Index Cond: (("endDate")::date >= '2019-08-07'::date)
  • Filter: ((("beginDate")::date <= '2019-08-14'::date) AND ('40'::smallint = ("countyNo")::smallint))
  • Rows Removed by Filter: 263
25. 2.695 2.695 ↓ 30.3 424 7

Index Scan using "Juror_PoolPanel" on "Juror" "J_3" (cost=0.43..1.67 rows=14 width=21) (actual time=0.018..0.385 rows=424 loops=7)

  • Index Cond: ((("P_1"."juryYear")::text = ("juryYear")::text) AND (("P_1"."poolId")::text = ("poolId")::text) AND (("P_1"."countyNo")::smallint = ("countyNo")::smallint) AND (("countyNo")::smallint = '40'::smallint))
26. 3.101 3.101 ↓ 30.3 424 7

Index Scan using "Juror_PoolPanel" on "Juror" "J_2" (cost=0.43..1.67 rows=14 width=21) (actual time=0.017..0.443 rows=424 loops=7)

  • Index Cond: ((("P_1"."juryYear")::text = ("juryYear")::text) AND (("P_1"."poolId")::text = ("poolId")::text) AND (("P_1"."countyNo")::smallint = ("countyNo")::smallint) AND (("countyNo")::smallint = '40'::smallint))
27. 14.845 14.845 ↑ 1.0 1 2,969

Index Scan using "Action_jurorSSENTaction" on "Action" "A_1" (cost=0.43..0.47 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=2,969)

  • Index Cond: ((("J_2"."jurorId")::integer = ("jurorId")::integer) AND (("J_2"."juryYear")::text = ("juryYear")::text) AND (("J_2"."countyNo")::smallint = ("countyNo")::smallint) AND (("countyNo")::smallint = '40'::smallint))
  • Filter: (("J_2"."poolId")::text = ("poolId")::text)
  • Rows Removed by Filter: 0
28. 30.728 71.292 ↑ 1.0 153,615 1

Hash (cost=2,768.37..2,768.37 rows=153,789 width=15) (actual time=71.292..71.292 rows=153,615 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9249kB
29. 40.564 40.564 ↑ 1.0 153,615 1

Index Only Scan using "Action_MFROM" on "Action" a2_1 (cost=0.42..2,768.37 rows=153,789 width=15) (actual time=0.017..40.564 rows=153,615 loops=1)

  • Index Cond: ("countyNo" = '40'::smallint)
  • Heap Fetches: 7
30.          

SubPlan (forGroupAggregate)

31. 0.119 2.443 ↑ 1.0 1 7

Aggregate (cost=1.66..1.67 rows=1 width=8) (actual time=0.349..0.349 rows=1 loops=7)

32. 2.324 2.324 ↓ 53.5 214 7

Index Scan using "Juror_PoolPanel" on "Juror" "J_4" (cost=0.43..1.65 rows=4 width=0) (actual time=0.018..0.332 rows=214 loops=7)

  • Index Cond: ((("juryYear")::text = ("X"."juryYear")::text) AND (("poolId")::text = ("X"."poolId")::text) AND (("countyNo")::smallint = ("X"."countyNo")::smallint))
  • Filter: "isQualified
  • Rows Removed by Filter: 210
Planning time : 6.686 ms
Execution time : 4,510.005 ms