explain.depesz.com

PostgreSQL's explain analyze made readable

Result: omlm

Settings
# exclusive inclusive rows x rows loops node
1. 67.022 1,075.229 ↓ 1.0 62,135 1

Unique (cost=101,862.61..117,275.61 rows=61,652 width=666) (actual time=985.091..1,075.229 rows=62,135 loops=1)

2. 192.105 1,008.207 ↓ 1.0 62,135 1

Sort (cost=101,862.61..102,016.74 rows=61,652 width=666) (actual time=985.090..1,008.207 rows=62,135 loops=1)

  • Sort Key: measuringp0_.valid_from DESC, measuringp0_.id, measuringp1_.id, location4_.id, register2_.id, measuremen3_.id, equations5_.id, measuringp0_.coefficient, measuringp0_.final_value, measuringp0_.initial_value, measuringp0_.invoice_meter_fk, measuringp0_.measurement_fk, measuringp0_.register_fk, measuringp0_.valid_to, measuringp1_.is_hidden, measuringp1_.name, measuringp1_.notes, measuringp1_.type, measuringp1_.unit_fk, measuringp1_.valid_from, measuringp1_.valid_to, measuringp1_.value_type_fk, location4_.code, location4_.cons_location_code, location4_.disconnection_date, location4_.domain, location4_.fuse_size, location4_.grid_zone, location4_.scw_inquiry_res, location4_.invoice_group, location4_.is_disconnected, location4_.is_hidden, location4_.meas_target_type, location4_.name, location4_.notes_1, location4_.notes_2, location4_.possessor_location_code, location4_.room_number, location4_.sales_contract_type, location4_.source_text, location4_.subscription_flow, location4_.subscription_id, location4_.subscription_power, location4_.target_text, location4_.tariff_name, location4_.transformer, location4_.valid_from, location4_.valid_to, grid6_.balance_responsible, grid6_.company_fk, grid6_.inhouse_code, grid6_.inhouse_code_nbs_seller, grid6_.scw_inquiry_templ, grid6_.is_auto_balance_calc_enabled, grid6_.is_hidden, grid6_.is_prod_cons_transfer_enabled, grid6_.local_seller, grid6_.name, grid6_.notes, grid6_.periodic_invocation_fk, grid6_.possessor, grid6_.supplyer, grid6_.type_fk, register2_.aquisition_method_fk, register2_.code, register2_.coefficient, register2_.first_dirty_value, register2_.first_value, register2_.fraction_digit_count, register2_.group_fk, register2_.input_type_fk, register2_.integer_digit_count, register2_.last_dirty_value, register2_.last_import_date, register2_.last_value, register2_.measure_period_fk, register2_.meter_fk, register2_.name, register2_.sort_order, register2_.unit_fk, register2_.value_type_fk, measuremen3_.allowed_status_mask, measuremen3_.code, measuremen3_.grid_fk, measuremen3_.is_verified, measuremen3_.last_import_date, measuremen3_.last_import_filter_fk, measuremen3_.measure_period_fk, measuremen3_.name, measuremen3_.notes, measuremen3_.target_text, measuremen3_.unit_fk, measuremen3_.utc_offset, measuremen3_.value_type_fk, equations5_.expression, equations5_.last_calculated, equations5_.measurement_fk, equations5_.valid_from, equations5_.valid_to
  • Sort Method: external merge Disk: 17720kB
3. 32.848 816.102 ↓ 1.0 62,135 1

Nested Loop (cost=31,165.90..78,621.12 rows=61,652 width=666) (actual time=453.002..816.102 rows=62,135 loops=1)

4. 0.028 0.028 ↑ 1.0 1 1

Index Scan using grid_pkey on grid grid6_ (cost=0.15..8.17 rows=1 width=103) (actual time=0.025..0.028 rows=1 loops=1)

  • Index Cond: (id = 3084)
5. 58.031 783.226 ↓ 1.0 62,135 1

Gather (cost=31,165.75..77,996.44 rows=61,652 width=555) (actual time=452.973..783.226 rows=62,135 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 11.777 725.195 ↑ 1.2 20,712 3

Hash Left Join (cost=30,165.75..70,831.24 rows=25,688 width=555) (actual time=448.079..725.195 rows=20,712 loops=3)

  • Hash Cond: (measuremen3_.id = equations5_.measurement_fk)
7. 75.780 710.822 ↑ 1.2 20,712 3

Nested Loop Left Join (cost=30,014.47..70,435.79 rows=25,688 width=514) (actual time=445.418..710.822 rows=20,712 loops=3)

8. 12.432 635.039 ↑ 1.2 20,712 3

Hash Left Join (cost=30,014.04..51,613.46 rows=25,688 width=412) (actual time=445.380..635.039 rows=20,712 loops=3)

  • Hash Cond: (measuringp0_.measurement_fk = measuremen3_.id)
9. 108.003 566.059 ↑ 1.2 20,712 3

Hash Join (cost=26,820.30..44,994.41 rows=25,688 width=319) (actual time=388.644..566.059 rows=20,712 loops=3)

  • Hash Cond: (measuringp1_.id = measuringp0_.measuring_point_fk)
10. 94.501 232.568 ↑ 1.3 20,712 3

Hash Join (cost=12,341.86..24,100.90 rows=26,834 width=268) (actual time=161.508..232.568 rows=20,712 loops=3)

  • Hash Cond: (measuringp1_.location_fk = location4_.id)
11. 35.944 35.944 ↑ 1.3 116,837 3

Parallel Seq Scan on measuring_point measuringp1_ (cost=0.00..5,277.23 rows=146,123 width=53) (actual time=0.011..35.944 rows=116,837 loops=3)

12. 34.105 102.123 ↑ 1.0 60,256 3

Hash (cost=9,798.17..9,798.17 rows=60,855 width=215) (actual time=102.123..102.123 rows=60,256 loops=3)

  • Buckets: 16384 Batches: 4 Memory Usage: 1793kB
13. 68.018 68.018 ↑ 1.0 60,256 3

Seq Scan on location location4_ (cost=0.00..9,798.17 rows=60,855 width=215) (actual time=0.054..68.018 rows=60,256 loops=3)

  • Filter: (grid_fk = 3084)
  • Rows Removed by Filter: 275956
14. 127.454 225.488 ↑ 1.0 332,845 3

Hash (cost=7,002.95..7,002.95 rows=335,719 width=51) (actual time=225.488..225.488 rows=332,845 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 1937kB
15. 98.034 98.034 ↑ 1.0 332,845 3

Seq Scan on measuring_point_target measuringp0_ (cost=0.00..7,002.95 rows=335,719 width=51) (actual time=0.070..98.034 rows=332,845 loops=3)

  • Filter: ((valid_from IS NOT NULL) AND (valid_to IS NULL))
  • Rows Removed by Filter: 46485
16. 34.433 56.548 ↑ 1.0 64,344 3

Hash (cost=1,446.44..1,446.44 rows=64,344 width=93) (actual time=56.548..56.548 rows=64,344 loops=3)

  • Buckets: 32768 Batches: 4 Memory Usage: 1870kB
17. 22.115 22.115 ↑ 1.0 64,344 3

Seq Scan on measurement measuremen3_ (cost=0.00..1,446.44 rows=64,344 width=93) (actual time=0.036..22.115 rows=64,344 loops=3)

18. 0.003 0.003 ↑ 1.0 1 62,135

Index Scan using register_pkey on register register2_ (cost=0.43..0.73 rows=1 width=102) (actual time=0.003..0.003 rows=1 loops=62,135)

  • Index Cond: (measuringp0_.register_fk = id)
19. 1.396 2.596 ↑ 1.0 4,857 3

Hash (cost=90.57..90.57 rows=4,857 width=41) (actual time=2.596..2.596 rows=4,857 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 383kB
20. 1.200 1.200 ↑ 1.0 4,857 3

Seq Scan on equation equations5_ (cost=0.00..90.57 rows=4,857 width=41) (actual time=0.044..1.200 rows=4,857 loops=3)

Planning time : 14.814 ms
Execution time : 1,082.494 ms