explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3HTs

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.001 ↑ 1.0 1 1

Result (cost=138,061.48..138,061.49 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

2.          

CTE percentage_quality

3. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on base_configuration base_configuration (cost=0..1.48 rows=1 width=6) (never executed)

  • Filter: ((base_configuration.fk_domain = 1) AND ((base_configuration.configuration_key)::text = 'percentage_incentives_effectiveness'::text))
4.          

CTE is_incentives_affected

5. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on base_configuration base_configuration_1 (cost=0..1.48 rows=1 width=6) (actual time=0.022..0.023 rows=1 loops=1)

  • Filter: ((base_configuration_1.fk_domain = 1) AND ((base_configuration_1.configuration_key)::text = 'is_incentives_affected_by_effectiveness'::text))
  • Buffers: shared hit=1
6.          

CTE production_efficiency_last_month

7. 13,722.479 13,722.479 ↑ 1.1 33,118 1

Index Scan using index_production_efficiency_date_taken_fk_employee on production_efficiency production_efficiency (cost=0.43..36,048.84 rows=35,452 width=32) (actual time=0.064..13,722.479 rows=33,118 loops=1)

  • Index Cond: (production_efficiency.date_taken >= (date_trunc('month'::text, now()) - '1 mon'::interval))
  • Filter: (production_efficiency.fk_domain = 1)
  • Buffers: shared hit=10191513
8.          

CTE incentives_by_hour

9. 84.178 15,695.980 ↓ 869.2 31,292 1

Aggregate (cost=97,672.06..97,672.42 rows=36 width=24) (actual time=15,683.241..15,695.98 rows=31,292 loops=1)

  • Buffers: shared hit=10202148, temp read=1988 written=2001
10.          

Initplan (forAggregate)

11. 0.026 0.026 ↑ 1.0 1 1

CTE Scan on is_incentives_affected is_incentives_affected (cost=0..0.02 rows=1 width=32) (actual time=0.025..0.026 rows=1 loops=1)

  • Buffers: shared hit=1
12. 0.000 0.000 ↓ 0.0 0 0

CTE Scan on percentage_quality percentage_quality (cost=0..0.02 rows=1 width=32) (never executed)

13. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on is_incentives_affected is_incentives_affected_1 (cost=0..0.02 rows=1 width=32) (actual time=0..0 rows=1 loops=1)

14. 127.888 15,611.776 ↓ 2,795.8 100,648 1

Merge Join (cost=90,712.18..97,671.28 rows=36 width=24) (actual time=15,504.253..15,611.776 rows=100,648 loops=1)

  • Filter: (((($3)::text = 'true'::text) AND (quality_effectiveness_hour.effectiveness_units >= ($4)::real)) OR (($5)::text = 'false'::text))
  • Buffers: shared hit=10202148, temp read=1988 written=2001
15. 610.303 757.770 ↑ 1.5 460,420 1

Sort (cost=89,211.55..90,936.48 rows=689,974 width=16) (actual time=635.597..757.77 rows=460,420 loops=1)

  • Sort Key: quality_effectiveness_hour.date_taken, quality_effectiveness_hour.fk_employee, quality_effectiveness_hour.fk_hour
  • Sort Method: external merge Disk: 11680kB
  • Buffers: shared hit=8489, temp read=1465 written=1465
16. 147.467 147.467 ↑ 1.5 460,427 1

Seq Scan on quality_effectiveness_hour quality_effectiveness_hour (cost=0..15,388.74 rows=689,974 width=16) (actual time=0.009..147.467 rows=460,427 loops=1)

  • Buffers: shared hit=8489
17. 130.702 14,726.118 ↓ 18.5 100,648 1

Sort (cost=1,500.63..1,514.26 rows=5,450 width=24) (actual time=14,700.743..14,726.118 rows=100,648 loops=1)

  • Sort Key: pe.date_taken, pe.fk_employee, pe.fk_hour
  • Sort Method: external sort Disk: 2936kB
  • Buffers: shared hit=10193658, temp read=445 written=536
18. 829.178 14,595.416 ↓ 14.5 79,009 1

Hash Join (cost=33.91..1,162.4 rows=5,450 width=24) (actual time=15.123..14,595.416 rows=79,009 loops=1)

  • Buffers: shared hit=10193658, temp written=169
19. 13,751.201 13,751.201 ↑ 1.1 33,118 1

CTE Scan on production_efficiency_last_month pe (cost=0..709.04 rows=35,452 width=24) (actual time=0.067..13,751.201 rows=33,118 loops=1)

  • Buffers: shared hit=10191513, temp written=169
20. 3.139 15.037 ↓ 95.6 12,138 1

Hash (cost=32.32..32.32 rows=127 width=12) (actual time=15.037..15.037 rows=12,138 loops=1)

  • Buffers: shared hit=2145
21. 6.314 11.898 ↓ 95.6 12,138 1

Nested Loop (cost=0..32.32 rows=127 width=12) (actual time=0.037..11.898 rows=12,138 loops=1)

  • Buffers: shared hit=2145
22. 0.111 0.382 ↓ 102.0 102 1

Nested Loop (cost=0..7.91 rows=1 width=16) (actual time=0.025..0.382 rows=102 loops=1)

  • Buffers: shared hit=105
23. 0.169 0.169 ↓ 102.0 102 1

Seq Scan on incentives incentives (cost=0..6.88 rows=1 width=12) (actual time=0.017..0.169 rows=102 loops=1)

  • Filter: (incentives.active AND (incentives.fk_domain = 1) AND (lower((incentives.type_incentive)::text) = 'hora'::text))
  • Buffers: shared hit=3
24. 0.102 0.102 ↑ 1.0 1 102

Seq Scan on incentives_group incentives_group (cost=0..1.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=102)

  • Filter: incentives_group.active
  • Buffers: shared hit=102
25. 5.202 5.202 ↑ 1.1 124 102

Seq Scan on employee employee (cost=0..22.75 rows=133 width=8) (actual time=0.001..0.051 rows=124 loops=102)

  • Filter: employee.active
  • Buffers: shared hit=2040
26.          

CTE incentives_by_day

27. 70.142 1,011.538 ↓ 869.2 31,292 1

Aggregate (cost=4,182.02..4,182.38 rows=36 width=20) (actual time=993.302..1,011.538 rows=31,292 loops=1)

  • Buffers: shared hit=470113, temp read=171 written=1
28.          

Initplan (forAggregate)

29. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on is_incentives_affected is_incentives_affected_2 (cost=0..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

30. 0.000 0.000 ↓ 0.0 0 0

CTE Scan on percentage_quality percentage_quality_1 (cost=0..0.02 rows=1 width=32) (never executed)

31. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on is_incentives_affected is_incentives_affected_3 (cost=0..0.02 rows=1 width=32) (actual time=0..0 rows=1 loops=1)

32. 15.781 941.394 ↓ 1,655.6 59,603 1

Nested Loop (cost=32.75..4,181.33 rows=36 width=20) (actual time=10.332..941.394 rows=59,603 loops=1)

  • Buffers: shared hit=470113, temp read=171 written=1
33. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on incentives_group incentives_group_1 (cost=0..1.01 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: incentives_group_1.active
  • Buffers: shared hit=1
34. 71.709 925.601 ↓ 1,655.6 59,603 1

Nested Loop (cost=32.75..4,179.87 rows=36 width=28) (actual time=10.314..925.601 rows=59,603 loops=1)

  • Filter: (((($7)::text = 'true'::text) AND (quality_effectiveness_day.effectiveness_units >= ($8)::real)) OR (($9)::text = 'false'::text))
  • Buffers: shared hit=470112, temp read=171 written=1
35. 596.222 615.480 ↓ 10.9 59,603 1

Hash Join (cost=32.33..1,160.82 rows=5,450 width=28) (actual time=10.283..615.48 rows=59,603 loops=1)

  • Buffers: shared hit=1443, temp read=171 written=1
36. 9.010 9.010 ↑ 1.1 33,118 1

CTE Scan on production_efficiency_last_month pe_1 (cost=0..709.04 rows=35,452 width=20) (actual time=0.014..9.01 rows=33,118 loops=1)

  • Buffers: temp read=171 written=1
37. 2.157 10.248 ↓ 67.5 8,568 1

Hash (cost=30.74..30.74 rows=127 width=20) (actual time=10.248..10.248 rows=8,568 loops=1)

  • Buffers: shared hit=1443
38. 4.363 8.091 ↓ 67.5 8,568 1

Nested Loop (cost=0..30.74 rows=127 width=20) (actual time=0.046..8.091 rows=8,568 loops=1)

  • Buffers: shared hit=1443
39. 0.128 0.128 ↓ 72.0 72 1

Seq Scan on incentives incentives_1 (cost=0..6.33 rows=1 width=12) (actual time=0.033..0.128 rows=72 loops=1)

  • Filter: (incentives_1.active AND (lower((incentives_1.type_incentive)::text) = 'diario'::text))
  • Buffers: shared hit=3
40. 3.600 3.600 ↑ 1.1 124 72

Seq Scan on employee employee_1 (cost=0..22.75 rows=133 width=8) (actual time=0.001..0.05 rows=124 loops=72)

  • Filter: employee_1.active
  • Buffers: shared hit=1440
41. 238.412 238.412 ↑ 1.0 1 59,603

Index Scan using unique_quality_effectiveness_day on quality_effectiveness_day quality_effectiveness_day (cost=0.42..0.53 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=59,603)

  • Index Cond: ((pe_1.date_taken = quality_effectiveness_day.date_taken) AND (pe_1.fk_employee = quality_effectiveness_day.fk_employee))
  • Buffers: shared hit=468669
42.          

CTE union_incentives

43. 40.348 16,800.816 ↓ 1,117.6 31,292 1

Hash Join (cost=3.12..4.25 rows=28 width=68) (actual time=16,727.352..16,800.816 rows=31,292 loops=1)

  • Buffers: shared hit=10672262, temp read=2377 written=2507
44. 1,026.433 1,026.433 ↓ 869.2 31,292 1

CTE Scan on incentives_by_day c (cost=0..0.72 rows=36 width=24) (actual time=993.305..1,026.433 rows=31,292 loops=1)

  • Buffers: shared hit=470113, temp read=171 written=130
45. 10.831 15,734.035 ↓ 1,117.6 31,292 1

Hash (cost=2.77..2.77 rows=28 width=44) (actual time=15,734.035..15,734.035 rows=31,292 loops=1)

  • Buffers: shared hit=10202149, temp read=1988 written=2291
46. 13.114 15,723.204 ↓ 1,117.6 31,292 1

Hash Join (cost=1.63..2.77 rows=28 width=44) (actual time=15,683.291..15,723.204 rows=31,292 loops=1)

  • Buffers: shared hit=10202149, temp read=1988 written=2161
47. 15,710.062 15,710.062 ↓ 869.2 31,292 1

CTE Scan on incentives_by_hour h (cost=0..0.72 rows=36 width=28) (actual time=15,683.243..15,710.062 rows=31,292 loops=1)

  • Buffers: shared hit=10202148, temp read=1988 written=2161
48. 0.011 0.028 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=20) (actual time=0.028..0.028 rows=28 loops=1)

  • Buffers: shared hit=1
49. 0.017 0.017 ↑ 1.0 28 1

Seq Scan on hour hour (cost=0..1.28 rows=28 width=20) (actual time=0.009..0.017 rows=28 loops=1)

  • Buffers: shared hit=1
50.          

CTE upset

51. 353.316 724.495 ↓ 224.0 2,016 1

ModifyTable (cost=0.42..75.55 rows=9 width=296) (actual time=0.645..724.495 rows=2,016 loops=1)

  • Buffers: shared hit=670555 read=30 dirtied=66, temp read=254 written=1
52. 9.659 371.179 ↓ 224.0 2,016 1

Nested Loop (cost=0.42..75.55 rows=9 width=296) (actual time=0.339..371.179 rows=2,016 loops=1)

  • Buffers: shared hit=307817, temp read=254 written=1
53. 17.308 17.308 ↓ 1,117.6 31,292 1

CTE Scan on union_incentives union_incentives (cost=0..0.56 rows=28 width=128) (actual time=0.026..17.308 rows=31,292 loops=1)

  • Buffers: temp read=254 written=1
54. 344.212 344.212 ↓ 0.0 0 31,292

Index Scan using unique_production_aggregate_incentives on production_incentives h_1 (cost=0.42..2.66 rows=1 width=188) (actual time=0.009..0.011 rows=0 loops=31,292)

  • Index Cond: ((h_1.date_taken = union_incentives.date_taken) AND (h_1.fk_hour = union_incentives.fk_hour) AND (h_1.fk_employee = union_incentives.fk_employee))
  • Filter: ((h_1.fk_domain = 1) AND ((h_1.incentives_hour <> union_incentives.incentives_by_hour) OR ((h_1.incentives_hour IS NULL) AND (union_incentives.incentives_by_hour IS NOT NULL)) OR ((h_1.incentives_hour IS NOT NULL) AND (union_incentives.incentives_by_hour IS NULL)) OR (h_1.incentives_day <> union_incentives.incentives_by_day) OR ((h_1.incentives_day IS NULL) AND (union_incentives.incentives_by_day IS NOT NULL)) OR ((h_1.incentives_day IS NOT NULL) AND (union_incentives.incentives_by_day IS NULL)) OR (h_1.minuts_hour <> union_incentives.real_minuts_hour) OR ((h_1.minuts_hour IS NULL) AND (union_incentives.real_minuts_hour IS NOT NULL)) OR ((h_1.minuts_hour IS NOT NULL) AND (union_incentives.real_minuts_hour IS NULL)) OR (h_1.minuts_day <> union_incentives.real_minuts_day) OR ((h_1.minuts_day IS NULL) AND (union_incentives.real_minuts_day IS NOT NULL) AND (h_1.minuts_day IS NOT NULL) AND (union_incentives.real_minuts_day IS NULL))))
  • Buffers: shared hit=307817
55.          

CTE inset

56. 0.002 17,278.445 ↓ 0.0 0 1

ModifyTable (cost=0.42..75.09 rows=1 width=73) (actual time=17,278.445..17,278.445 rows=0 loops=1)

  • Buffers: shared hit=10980078, temp read=2377 written=2759
57. 0.002 17,278.443 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=0.42..75.09 rows=1 width=73) (actual time=17,278.443..17,278.443 rows=0 loops=1)

  • Buffers: shared hit=10980078, temp read=2377 written=2759
58. 18.155 17,278.441 ↓ 0.0 0 1

Nested Loop (cost=0.42..75.07 rows=1 width=52) (actual time=17,278.441..17,278.441 rows=0 loops=1)

  • Filter: (h_2.* IS NULL)
  • Buffers: shared hit=10980078, temp read=2377 written=2759
59. 16,822.198 16,822.198 ↓ 1,117.6 31,292 1

CTE Scan on union_incentives c_1 (cost=0..0.56 rows=28 width=52) (actual time=16,727.355..16,822.198 rows=31,292 loops=1)

  • Buffers: shared hit=10672262, temp read=2377 written=2759
60. 438.088 438.088 ↑ 1.0 1 31,292

Index Scan using unique_production_aggregate_incentives on production_incentives h_2 (cost=0.42..2.65 rows=1 width=246) (actual time=0.006..0.014 rows=1 loops=31,292)

  • Index Cond: ((h_2.date_taken = c_1.date_taken) AND (h_2.fk_hour = c_1.fk_hour) AND (h_2.fk_employee = c_1.fk_employee))
  • Filter: (h_2.fk_domain = 1)
  • Buffers: shared hit=307816