explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sihZ

Settings
# exclusive inclusive rows x rows loops node
1. 1.330 368.393 ↓ 49.0 29,697 1

Append (cost=506.33..1,679.34 rows=606 width=517) (actual time=278.522..368.393 rows=29,697 loops=1)

2. 11.533 294.503 ↓ 52.2 29,697 1

Hash Join (cost=506.33..938.11 rows=569 width=517) (actual time=278.522..294.503 rows=29,697 loops=1)

  • Hash Cond: ("*VALUES*_1".column1 = syn_observation.observed_item_id)
3. 4.463 4.463 ↑ 1.0 24,348 1

Values Scan on "*VALUES*_1" (cost=0.00..304.35 rows=24,348 width=4) (actual time=0.003..4.463 rows=24,348 loops=1)

4. 18.608 278.507 ↓ 145.3 47,499 1

Hash (cost=502.24..502.24 rows=327 width=229) (actual time=278.507..278.507 rows=47,499 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9,233kB
5. 12.482 259.899 ↓ 145.3 47,499 1

Hash Left Join (cost=19.65..502.24 rows=327 width=229) (actual time=0.219..259.899 rows=47,499 loops=1)

  • Hash Cond: (syn_observation.document_id = syn_document.id)
6. 10.091 247.380 ↓ 145.3 47,499 1

Hash Left Join (cost=13.82..495.53 rows=327 width=205) (actual time=0.176..247.380 rows=47,499 loops=1)

  • Hash Cond: (syn_observation.id = syn_observation_attachment.observation_id)
7. 11.454 237.283 ↓ 145.3 47,499 1

Hash Left Join (cost=12.53..493.00 rows=327 width=203) (actual time=0.161..237.283 rows=47,499 loops=1)

  • Hash Cond: (syn_observation.quantity_conc_unit = synconcunit.id)
8. 11.068 225.816 ↓ 145.3 47,499 1

Hash Join (cost=10.27..489.82 rows=327 width=205) (actual time=0.141..225.816 rows=47,499 loops=1)

  • Hash Cond: (syn_observation.unit_id = syn_observation_unit.id)
9. 11.529 214.725 ↓ 145.3 47,499 1

Hash Join (cost=8.01..486.64 rows=327 width=207) (actual time=0.109..214.725 rows=47,499 loops=1)

  • Hash Cond: (syn_observation.type_id = syn_observation_type.id)
10. 6.911 203.151 ↓ 145.3 47,499 1

Nested Loop (cost=1.41..479.17 rows=327 width=206) (actual time=0.055..203.151 rows=47,499 loops=1)

11. 10.418 148.741 ↓ 145.3 47,499 1

Nested Loop (cost=1.13..380.46 rows=327 width=188) (actual time=0.048..148.741 rows=47,499 loops=1)

12. 34.284 90.824 ↓ 145.3 47,499 1

Nested Loop (cost=0.84..267.17 rows=327 width=183) (actual time=0.038..90.824 rows=47,499 loops=1)

13. 3.670 9.041 ↓ 108.2 47,499 1

Nested Loop (cost=0.42..25.34 rows=439 width=16) (actual time=0.026..9.041 rows=47,499 loops=1)

14. 0.007 0.007 ↑ 1.0 3 1

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4) (actual time=0.003..0.007 rows=3 loops=1)

15. 5.364 5.364 ↓ 108.4 15,833 3

Index Only Scan using ld_addable_columns_observatio_addable_column_id_observation_key on ld_addable_columns_observations (cost=0.42..6.97 rows=146 width=16) (actual time=0.013..1.788 rows=15,833 loops=3)

  • Index Cond: (addable_column_id = "*VALUES*".column1)
  • Heap Fetches: 0
16. 47.499 47.499 ↑ 1.0 1 47,499

Index Scan using syn_observation_pk on syn_observation (cost=0.42..0.55 rows=1 width=175) (actual time=0.001..0.001 rows=1 loops=47,499)

  • Index Cond: (id = ld_addable_columns_observations.observation_id)
  • Filter: ((is_published)::text = 'Y'::text)
17. 47.499 47.499 ↑ 1.0 1 47,499

Index Scan using syn_observation_protocol_id_pk on syn_observation_protocol (cost=0.29..0.35 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=47,499)

  • Index Cond: (id = syn_observation.protocol_id)
18. 47.499 47.499 ↑ 1.0 1 47,499

Index Scan using syn_phenomenon_type_id_pk on syn_phenomenon_type (cost=0.28..0.30 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=47,499)

  • Index Cond: (id = syn_observation_protocol.phenomenon_type_id)
19. 0.025 0.045 ↑ 1.0 204 1

Hash (cost=4.04..4.04 rows=204 width=17) (actual time=0.045..0.045 rows=204 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
20. 0.020 0.020 ↑ 1.0 204 1

Seq Scan on syn_observation_type (cost=0.00..4.04 rows=204 width=17) (actual time=0.003..0.020 rows=204 loops=1)

21. 0.010 0.023 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=14) (actual time=0.023..0.023 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
22. 0.013 0.013 ↑ 1.0 56 1

Seq Scan on syn_observation_unit (cost=0.00..1.56 rows=56 width=14) (actual time=0.008..0.013 rows=56 loops=1)

23. 0.007 0.013 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=14) (actual time=0.013..0.013 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
24. 0.006 0.006 ↑ 1.0 56 1

Seq Scan on syn_observation_unit synconcunit (cost=0.00..1.56 rows=56 width=14) (actual time=0.001..0.006 rows=56 loops=1)

25. 0.002 0.006 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=10) (actual time=0.006..0.006 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 1.0 13 1

Seq Scan on syn_observation_attachment (cost=0.00..1.13 rows=13 width=10) (actual time=0.002..0.004 rows=13 loops=1)

27. 0.019 0.037 ↑ 1.0 126 1

Hash (cost=4.26..4.26 rows=126 width=40) (actual time=0.037..0.037 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
28. 0.018 0.018 ↑ 1.0 126 1

Seq Scan on syn_document (cost=0.00..4.26 rows=126 width=40) (actual time=0.004..0.018 rows=126 loops=1)

29. 0.001 72.560 ↓ 0.0 0 1

Nested Loop Left Join (cost=302.62..735.17 rows=37 width=517) (actual time=72.560..72.560 rows=0 loops=1)

30. 0.000 72.559 ↓ 0.0 0 1

Hash Left Join (cost=302.47..729.15 rows=37 width=205) (actual time=72.559..72.559 rows=0 loops=1)

  • Hash Cond: (syn_observation_1.quantity_conc_unit = synconcunit_1.id)
31. 0.019 72.559 ↓ 0.0 0 1

Hash Join (cost=300.21..726.78 rows=37 width=207) (actual time=72.559..72.559 rows=0 loops=1)

  • Hash Cond: (syn_observation_1.unit_id = syn_observation_unit_1.id)
32. 0.010 72.518 ↓ 0.0 0 1

Hash Join (cost=297.95..724.41 rows=37 width=209) (actual time=72.518..72.518 rows=0 loops=1)

  • Hash Cond: ("*VALUES*_3".column1 = syn_observation_1.observed_item_id)
33. 0.005 0.005 ↑ 24,348.0 1 1

Values Scan on "*VALUES*_3" (cost=0.00..304.35 rows=24,348 width=4) (actual time=0.005..0.005 rows=1 loops=1)

34. 0.000 72.503 ↓ 0.0 0 1

Hash (cost=297.69..297.69 rows=21 width=209) (actual time=72.503..72.503 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
35. 0.001 72.503 ↓ 0.0 0 1

Hash Left Join (cost=2.85..297.69 rows=21 width=209) (actual time=72.503..72.503 rows=0 loops=1)

  • Hash Cond: (syn_observation_1.id = syn_observation_attachment_1.observation_id)
36. 0.000 72.502 ↓ 0.0 0 1

Nested Loop (cost=1.56..296.31 rows=21 width=207) (actual time=72.502..72.502 rows=0 loops=1)

37. 0.001 72.502 ↓ 0.0 0 1

Nested Loop (cost=1.41..292.85 rows=21 width=206) (actual time=72.502..72.502 rows=0 loops=1)

38. 0.000 72.501 ↓ 0.0 0 1

Nested Loop (cost=1.13..286.51 rows=21 width=188) (actual time=72.501..72.501 rows=0 loops=1)

39. 17.697 72.501 ↓ 0.0 0 1

Nested Loop (cost=0.84..268.27 rows=21 width=183) (actual time=72.501..72.501 rows=0 loops=1)

40. 3.004 7.305 ↓ 108.2 47,499 1

Nested Loop (cost=0.42..25.34 rows=439 width=16) (actual time=0.026..7.305 rows=47,499 loops=1)

41. 0.005 0.005 ↑ 1.0 3 1

Values Scan on "*VALUES*_2" (cost=0.00..0.04 rows=3 width=4) (actual time=0.003..0.005 rows=3 loops=1)

42. 4.296 4.296 ↓ 108.4 15,833 3

Index Only Scan using ld_addable_columns_observatio_addable_column_id_observation_key on ld_addable_columns_observations ld_addable_columns_observations_1 (cost=0.42..6.97 rows=146 width=16) (actual time=0.011..1.432 rows=15,833 loops=3)

  • Index Cond: (addable_column_id = "*VALUES*_2".column1)
  • Heap Fetches: 0
43. 47.499 47.499 ↓ 0.0 0 47,499

Index Scan using syn_observation_pk on syn_observation syn_observation_1 (cost=0.42..0.55 rows=1 width=175) (actual time=0.001..0.001 rows=0 loops=47,499)

  • Index Cond: (id = ld_addable_columns_observations_1.observation_id)
  • Filter: (((is_published)::text = 'N'::text) AND (source_live_report_id = 893))
  • Rows Removed by Filter: 1
44. 0.000 0.000 ↓ 0.0 0

Index Scan using syn_observation_protocol_id_pk on syn_observation_protocol syn_observation_protocol_1 (cost=0.29..0.87 rows=1 width=21) (never executed)

  • Index Cond: (id = syn_observation_1.protocol_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using syn_phenomenon_type_id_pk on syn_phenomenon_type syn_phenomenon_type_1 (cost=0.28..0.30 rows=1 width=34) (never executed)

  • Index Cond: (id = syn_observation_protocol_1.phenomenon_type_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using syn_observation_type_pk on syn_observation_type syn_observation_type_1 (cost=0.14..0.16 rows=1 width=17) (never executed)

  • Index Cond: (id = syn_observation_1.type_id)
47. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.13..1.13 rows=13 width=10) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on syn_observation_attachment syn_observation_attachment_1 (cost=0.00..1.13 rows=13 width=10) (never executed)

49. 0.009 0.022 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=14) (actual time=0.022..0.022 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
50. 0.013 0.013 ↑ 1.0 56 1

Seq Scan on syn_observation_unit syn_observation_unit_1 (cost=0.00..1.56 rows=56 width=14) (actual time=0.008..0.013 rows=56 loops=1)

51. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.56..1.56 rows=56 width=14) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on syn_observation_unit synconcunit_1 (cost=0.00..1.56 rows=56 width=14) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using syn_document_id_pk on syn_document syn_document_1 (cost=0.14..0.16 rows=1 width=40) (never executed)

  • Index Cond: (syn_observation_1.document_id = id)
Planning time : 79.413 ms
Execution time : 370.834 ms