explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Imaq

Settings
# exclusive inclusive rows x rows loops node
1. 0.281 1,121.693 ↑ 14,791.0 1 1

Hash Join (cost=48,350.80..50,829.28 rows=14,791 width=8) (actual time=1,121.675..1,121.693 rows=1 loops=1)

  • Hash Cond: (ld_entity.id = ld_entities_projects.entity_id)
2. 72.418 1,004.430 ↑ 21,299.0 1 1

Hash Join (cost=46,545.05..48,689.26 rows=21,299 width=16) (actual time=1,004.414..1,004.430 rows=1 loops=1)

  • Hash Cond: (ld_entity.entity_id = (COALESCE(expanded_entity.entity_id, original_entity.entity_id)))
3. 70.702 70.702 ↑ 1.0 70,109 1

Seq Scan on ld_entity (cost=0.00..1,259.09 rows=70,109 width=16) (actual time=0.015..70.702 rows=70,109 loops=1)

4. 0.015 861.310 ↑ 21,299.0 1 1

Hash (cost=46,278.81..46,278.81 rows=21,299 width=32) (actual time=861.310..861.310 rows=1 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 257kB
5. 0.141 861.295 ↑ 21,299.0 1 1

HashAggregate (cost=45,852.83..46,065.82 rows=21,299 width=32) (actual time=861.259..861.295 rows=1 loops=1)

  • Group Key: COALESCE(expanded_entity.entity_id, original_entity.entity_id)
6. 64.995 861.154 ↑ 21,299.0 1 1

Nested Loop Semi Join (cost=5,846.57..45,799.58 rows=21,299 width=32) (actual time=861.055..861.154 rows=1 loops=1)

  • Join Filter: ((original_compound.jchem_structure_id = (unnest('{21167}'::integer[]))) OR (original_fragment.jchem_structure_id = (unnest('{21167}'::integer[]))))
  • Rows Removed by Join Filter: 21194
7. 52.690 753.769 ↑ 1.0 21,195 1

Hash Left Join (cost=5,846.57..8,611.94 rows=21,299 width=32) (actual time=537.646..753.769 rows=21,195 loops=1)

  • Hash Cond: (original_compound.canonical_structure_id = expanded_compound.canonical_structure_id)
  • Join Filter: ((ld_compound_unlink.virtual_compound_id IS NULL) AND (original_source.alternate_id = 'pri'::text) AND (original_compound.data_source_id <> expanded_compound.data_source_id))
  • Rows Removed by Join Filter: 21188
8. 43.393 263.962 ↑ 1.0 21,194 1

Hash Left Join (cost=1,137.87..3,429.61 rows=21,299 width=59) (actual time=100.411..263.962 rows=21,194 loops=1)

  • Hash Cond: (original_compound.id = ld_compound_unlink.virtual_compound_id)
9. 43.935 220.564 ↑ 1.0 21,194 1

Hash Left Join (cost=1,086.25..3,109.09 rows=21,299 width=59) (actual time=100.390..220.564 rows=21,194 loops=1)

  • Hash Cond: (original_entity.id = original_fragment.id)
10. 50.929 176.603 ↑ 1.0 21,194 1

Hash Left Join (cost=1,085.13..2,839.08 rows=21,299 width=59) (actual time=100.346..176.603 rows=21,194 loops=1)

  • Hash Cond: (original_entity.id = original_compound.id)
11. 31.756 31.756 ↑ 1.0 21,194 1

Seq Scan on ld_entity original_entity (cost=0.00..1,609.63 rows=21,299 width=16) (actual time=6.306..31.756 rows=21,194 loops=1)

  • Filter: ((entity_type <> 'SAMPLE'::text) AND (archived = 0))
  • Rows Removed by Filter: 48915
12. 24.648 93.918 ↑ 1.0 21,213 1

Hash (cost=819.97..819.97 rows=21,213 width=43) (actual time=93.918..93.918 rows=21,213 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1666kB
13. 47.541 69.270 ↑ 1.0 21,213 1

Hash Left Join (cost=1.09..819.97 rows=21,213 width=43) (actual time=0.040..69.270 rows=21,213 loops=1)

  • Hash Cond: (original_compound.data_source_id = original_source.id)
14. 21.709 21.709 ↑ 1.0 21,213 1

Seq Scan on syn_compound original_compound (cost=0.00..598.13 rows=21,213 width=32) (actual time=0.006..21.709 rows=21,213 loops=1)

15. 0.010 0.020 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=19) (actual time=0.020..0.020 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on ld_data_source original_source (cost=0.00..1.04 rows=4 width=19) (actual time=0.005..0.010 rows=4 loops=1)

17. 0.010 0.026 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=16) (actual time=0.026..0.026 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.016 0.016 ↑ 1.0 5 1

Seq Scan on ld_fragment original_fragment (cost=0.00..1.05 rows=5 width=16) (actual time=0.010..0.016 rows=5 loops=1)

19. 0.002 0.005 ↓ 0.0 0 1

Hash (cost=28.50..28.50 rows=1,850 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
20. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on ld_compound_unlink (cost=0.00..28.50 rows=1,850 width=8) (actual time=0.003..0.003 rows=0 loops=1)

21. 24.487 437.117 ↓ 1.0 21,067 1

Hash (cost=4,451.62..4,451.62 rows=20,566 width=24) (actual time=437.117..437.117 rows=21,067 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1411kB
22. 105.672 412.630 ↓ 1.0 21,069 1

Hash Right Join (cost=2,723.96..4,451.62 rows=20,566 width=24) (actual time=340.312..412.630 rows=21,069 loops=1)

  • Hash Cond: (expanded_entity.id = expanded_compound.id)
23. 71.160 71.160 ↑ 1.0 70,109 1

Seq Scan on ld_entity expanded_entity (cost=0.00..1,259.09 rows=70,109 width=16) (actual time=0.009..71.160 rows=70,109 loops=1)

24. 27.532 235.798 ↓ 1.0 21,069 1

Hash (cost=2,466.89..2,466.89 rows=20,566 width=24) (actual time=235.798..235.798 rows=21,069 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1409kB
25. 47.291 208.266 ↓ 1.0 21,069 1

Hash Join (cost=1,677.73..2,466.89 rows=20,566 width=24) (actual time=135.710..208.266 rows=21,069 loops=1)

  • Hash Cond: (expanded_compound.id = ld_entities_projects_1.entity_id)
26. 25.381 25.381 ↑ 1.0 21,187 1

Seq Scan on syn_compound expanded_compound (cost=0.00..704.19 rows=21,187 width=24) (actual time=0.005..25.381 rows=21,187 loops=1)

  • Filter: ((archived = 0) AND (reactant = 0))
  • Rows Removed by Filter: 26
27. 23.529 135.594 ↓ 1.1 21,096 1

Hash (cost=1,428.69..1,428.69 rows=19,923 width=8) (actual time=135.594..135.594 rows=21,096 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1081kB
28. 68.171 112.065 ↓ 1.1 21,096 1

HashAggregate (cost=1,229.46..1,428.69 rows=19,923 width=8) (actual time=87.934..112.065 rows=21,096 loops=1)

  • Group Key: ld_entities_projects_1.entity_id
29. 43.894 43.894 ↑ 1.0 37,112 1

Seq Scan on ld_entities_projects ld_entities_projects_1 (cost=0.00..1,136.30 rows=37,263 width=8) (actual time=0.008..43.894 rows=37,112 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Rows Removed by Filter: 11575
30. 42.372 42.390 ↑ 100.0 1 21,195

Materialize (cost=0.00..2.02 rows=100 width=4) (actual time=0.001..0.002 rows=1 loops=21,195)

31. 0.015 0.018 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.013..0.018 rows=1 loops=1)

32. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1)

33. 58.833 116.982 ↑ 1.0 48,686 1

Hash (cost=1,197.16..1,197.16 rows=48,687 width=8) (actual time=116.982..116.982 rows=48,686 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2414kB
34. 58.149 58.149 ↑ 1.0 48,686 1

Seq Scan on ld_entities_projects (cost=0.00..1,197.16 rows=48,687 width=8) (actual time=0.012..58.149 rows=48,686 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3,4}'::bigint[]))
  • Rows Removed by Filter: 1