explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AOGE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 50.672 840.751 ↑ 11,093.0 1 1

Hash Join (cost=11,207.37..12,698.04 rows=11,093 width=8) (actual time=840.735..840.751 rows=1 loops=1)

  • Hash Cond: (ld_entities_projects.entity_id = ld_entity.id)
2. 53.274 53.274 ↑ 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.011..53.274 rows=48,686 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3,4}'::bigint[]))
  • Rows Removed by Filter: 1
3. 0.016 736.805 ↑ 15,974.0 1 1

Hash (cost=11,007.69..11,007.69 rows=15,974 width=16) (actual time=736.805..736.805 rows=1 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
4. 72.197 736.789 ↑ 15,974.0 1 1

Hash Join (cost=8,863.48..11,007.69 rows=15,974 width=16) (actual time=736.772..736.789 rows=1 loops=1)

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

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

6. 0.016 594.258 ↑ 15,974.0 1 1

Hash (cost=8,663.81..8,663.81 rows=15,974 width=32) (actual time=594.258..594.258 rows=1 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
7. 0.145 594.242 ↑ 15,974.0 1 1

HashAggregate (cost=8,344.33..8,504.07 rows=15,974 width=32) (actual time=594.205..594.242 rows=1 loops=1)

  • Group Key: COALESCE(expanded_entity.entity_id, original_entity.entity_id)
8. 74.741 594.097 ↑ 15,974.0 1 1

Hash Right Join (cost=6,622.66..8,304.39 rows=15,974 width=32) (actual time=594.054..594.097 rows=1 loops=1)

  • Hash Cond: (expanded_entity.id = expanded_compound.id)
9. 70.447 70.447 ↑ 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.008..70.447 rows=70,109 loops=1)

10. 0.015 448.909 ↑ 15,974.0 1 1

Hash (cost=6,422.98..6,422.98 rows=15,974 width=16) (actual time=448.909..448.909 rows=1 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
11. 22.458 448.894 ↑ 15,974.0 1 1

Hash Right Join (cost=5,244.17..6,422.98 rows=15,974 width=16) (actual time=448.848..448.894 rows=1 loops=1)

  • Hash Cond: (expanded_compound.canonical_structure_id = original_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))
12. 47.660 211.319 ↓ 1.0 21,069 1

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

  • Hash Cond: (expanded_compound.id = ld_entities_projects_1.entity_id)
13. 25.023 25.023 ↑ 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.014..25.023 rows=21,187 loops=1)

  • Filter: ((archived = 0) AND (reactant = 0))
  • Rows Removed by Filter: 26
14. 23.744 138.636 ↓ 1.1 21,096 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1081kB
15. 70.909 114.892 ↓ 1.1 21,096 1

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

  • Group Key: ld_entities_projects_1.entity_id
16. 43.983 43.983 ↑ 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.983 rows=37,112 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Rows Removed by Filter: 11575
17. 0.010 215.117 ↑ 15,974.0 1 1

Hash (cost=3,366.76..3,366.76 rows=15,974 width=43) (actual time=215.117..215.117 rows=1 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
18. 0.025 215.107 ↑ 15,974.0 1 1

Hash Left Join (cost=3,156.94..3,366.76 rows=15,974 width=43) (actual time=208.696..215.107 rows=1 loops=1)

  • Hash Cond: (original_compound.id = ld_compound_unlink.virtual_compound_id)
19. 6.541 215.076 ↑ 15,974.0 1 1

Hash Right Join (cost=3,105.32..3,113.48 rows=15,974 width=43) (actual time=208.668..215.076 rows=1 loops=1)

  • Hash Cond: (original_fragment.id = original_entity.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 21193
20. 0.015 0.015 ↑ 1.0 5 1

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

21. 26.557 208.495 ↑ 1.0 21,194 1

Hash (cost=2,839.08..2,839.08 rows=21,299 width=59) (actual time=208.495..208.495 rows=21,194 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2004kB
22. 51.233 181.938 ↑ 1.0 21,194 1

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

  • Hash Cond: (original_entity.id = original_compound.id)
23. 31.908 31.908 ↑ 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=7.203..31.908 rows=21,194 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1666kB
25. 50.321 72.385 ↑ 1.0 21,213 1

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

  • Hash Cond: (original_compound.data_source_id = original_source.id)
26. 22.042 22.042 ↑ 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.009..22.042 rows=21,213 loops=1)

27. 0.012 0.022 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 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)

29.          

SubPlan (forHash Right Join)

30. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tmp (cost=0.00..1.77 rows=1 width=0) (never executed)

  • Filter: (original_compound.jchem_structure_id = tmp.structure_id)
31. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

33. 0.015 0.017 ↑ 100.0 1 1

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

34. 0.002 0.002 ↑ 1.0 1 1

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

35. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tmp_1 (cost=0.00..1.77 rows=1 width=0) (never executed)

  • Filter: (original_fragment.jchem_structure_id = tmp_1.structure_id)
36. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

38. 0.006 0.008 ↑ 100.0 1 1

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

39. 0.002 0.002 ↑ 1.0 1 1

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

40. 0.002 0.006 ↓ 0.0 0 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
41. 0.004 0.004 ↓ 0.0 0 1

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