explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qWXQ

Settings
# exclusive inclusive rows x rows loops node
1. 67.278 990.498 ↓ 1.0 62,135 1

Unique (cost=99,390.91..113,684.55 rows=60,824 width=617) (actual time=898.568..990.498 rows=62,135 loops=1)

2. 183.683 923.220 ↓ 1.0 62,135 1

Sort (cost=99,390.91..99,542.97 rows=60,824 width=617) (actual time=898.567..923.220 rows=62,135 loops=1)

  • Sort Key: measuringp0_.valid_from DESC, measuringp0_.id, measuringp1_.id, location4_.id, register2_.id, measuremen3_.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, grid5_.balance_responsible, grid5_.company_fk, grid5_.inhouse_code, grid5_.inhouse_code_nbs_seller, grid5_.scw_inquiry_templ, grid5_.is_auto_balance_calc_enabled, grid5_.is_hidden, grid5_.is_prod_cons_transfer_enabled, grid5_.local_seller, grid5_.name, grid5_.notes, grid5_.periodic_invocation_fk, grid5_.possessor, grid5_.supplyer, grid5_.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
  • Sort Method: external merge Disk: 17720kB
3. 30.297 739.537 ↓ 1.0 62,135 1

Nested Loop (cost=30,862.59..77,715.73 rows=60,824 width=617) (actual time=397.883..739.537 rows=62,135 loops=1)

4. 0.014 0.014 ↑ 1.0 1 1

Index Scan using grid_pkey on grid grid5_ (cost=0.15..8.17 rows=1 width=103) (actual time=0.013..0.014 rows=1 loops=1)

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

Gather (cost=30,862.44..77,099.32 rows=60,824 width=514) (actual time=397.868..709.226 rows=62,135 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 72.047 642.479 ↑ 1.2 20,712 3

Nested Loop Left Join (cost=29,862.44..70,016.92 rows=25,343 width=514) (actual time=391.755..642.479 rows=20,712 loops=3)

7. 13.541 570.429 ↑ 1.2 20,712 3

Hash Left Join (cost=29,862.01..51,348.08 rows=25,343 width=412) (actual time=391.727..570.429 rows=20,712 loops=3)

  • Hash Cond: (measuringp0_.measurement_fk = measuremen3_.id)
8. 103.383 515.277 ↑ 1.2 20,712 3

Hash Join (cost=26,668.27..44,761.39 rows=25,343 width=319) (actual time=349.947..515.277 rows=20,712 loops=3)

  • Hash Cond: (measuringp1_.id = measuringp0_.measuring_point_fk)
9. 85.585 202.876 ↑ 1.3 20,712 3

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

  • Hash Cond: (measuringp1_.location_fk = location4_.id)
10. 28.853 28.853 ↑ 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.012..28.853 rows=116,837 loops=3)

11. 31.104 88.438 ↑ 1.0 60,256 3

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

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

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

  • Filter: (grid_fk = 3084)
  • Rows Removed by Filter: 275956
13. 119.155 209.018 ↓ 1.0 332,845 3

Hash (cost=6,951.30..6,951.30 rows=331,209 width=51) (actual time=209.018..209.018 rows=332,845 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 1937kB
14. 89.863 89.863 ↓ 1.0 332,845 3

Seq Scan on measuring_point_target measuringp0_ (cost=0.00..6,951.30 rows=331,209 width=51) (actual time=0.060..89.863 rows=332,845 loops=3)

  • Filter: ((valid_from IS NOT NULL) AND (valid_to IS NULL))
  • Rows Removed by Filter: 46485
15. 25.565 41.611 ↑ 1.0 64,344 3

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

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

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

17. 0.003 0.003 ↑ 1.0 1 62,135

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

  • Index Cond: (measuringp0_.register_fk = id)
Planning time : 9.723 ms
Execution time : 999.146 ms