explain.depesz.com

PostgreSQL's explain analyze made readable

Result: itmx : After R-Group

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 1,388.437 ↑ 859.5 2 1

Nested Loop Left Join (cost=12,011.58..14,385.73 rows=1,719 width=8) (actual time=1,372.658..1,388.437 rows=2 loops=1)

  • Filter: ((syn_compound.id IS NULL) OR (syn_compound.reactant = 0))
2. 22.198 1,388.385 ↑ 859.5 2 1

Hash Semi Join (cost=12,011.29..13,695.96 rows=1,719 width=16) (actual time=1,372.618..1,388.385 rows=2 loops=1)

  • Hash Cond: (ld_entity.entity_id = ld_entity_1.entity_id)
3. 32.403 32.403 ↑ 1.0 21,193 1

Seq Scan on ld_entity (cost=0.00..1,609.63 rows=21,299 width=16) (actual time=7.812..32.403 rows=21,193 loops=1)

  • Filter: ((entity_type = ANY ('{COMPOUND,FRAGMENT}'::text[])) AND (archived = 0))
  • Rows Removed by Filter: 48917
4. 0.016 1,333.784 ↑ 1,886.7 3 1

Hash (cost=11,940.54..11,940.54 rows=5,660 width=40) (actual time=1,333.784..1,333.784 rows=3 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 65kB
5. 37.920 1,333.768 ↑ 1,886.7 3 1

Hash Join (cost=10,607.90..11,940.54 rows=5,660 width=40) (actual time=1,280.643..1,333.768 rows=3 loops=1)

  • Hash Cond: (ld_entities_projects.entity_id = ld_entity_1.id)
6. 42.143 42.143 ↑ 1.0 37,113 1

Seq Scan on ld_entities_projects (cost=0.00..1,136.30 rows=37,263 width=8) (actual time=0.010..42.143 rows=37,113 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Rows Removed by Filter: 11575
7. 0.018 1,253.705 ↑ 5,325.0 2 1

Hash (cost=10,474.78..10,474.78 rows=10,650 width=48) (actual time=1,253.705..1,253.705 rows=2 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
8. 72.101 1,253.687 ↑ 5,325.0 2 1

Hash Join (cost=8,330.57..10,474.78 rows=10,650 width=48) (actual time=1,238.700..1,253.687 rows=2 loops=1)

  • Hash Cond: (ld_entity_1.entity_id = (COALESCE(expanded_entity.entity_id, original_entity.entity_id)))
9. 70.819 70.819 ↓ 1.0 70,110 1

Seq Scan on ld_entity ld_entity_1 (cost=0.00..1,259.09 rows=70,109 width=16) (actual time=0.005..70.819 rows=70,110 loops=1)

10. 0.028 1,110.767 ↑ 5,325.0 2 1

Hash (cost=8,197.45..8,197.45 rows=10,650 width=32) (actual time=1,110.767..1,110.767 rows=2 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
11. 0.081 1,110.739 ↑ 5,325.0 2 1

HashAggregate (cost=7,984.45..8,090.95 rows=10,650 width=32) (actual time=1,110.712..1,110.739 rows=2 loops=1)

  • Group Key: COALESCE(expanded_entity.entity_id, original_entity.entity_id)
12. 73.322 1,110.658 ↑ 5,325.0 2 1

Hash Right Join (cost=6,329.32..7,957.82 rows=10,650 width=32) (actual time=1,110.614..1,110.658 rows=2 loops=1)

  • Hash Cond: (expanded_entity.id = expanded_compound.id)
13. 70.289 70.289 ↓ 1.0 70,110 1

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

14. 0.018 967.047 ↑ 5,325.0 2 1

Hash (cost=6,196.20..6,196.20 rows=10,650 width=16) (actual time=967.047..967.047 rows=2 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
15. 22.066 967.029 ↑ 5,325.0 2 1

Hash Right Join (cost=5,121.56..6,196.20 rows=10,650 width=16) (actual time=966.986..967.029 rows=2 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))
  • Rows Removed by Join Filter: 1
16. 45.291 207.537 ↓ 1.0 21,069 1

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

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

  • Filter: ((archived = 0) AND (reactant = 0))
  • Rows Removed by Filter: 26
18. 23.329 137.188 ↓ 1.1 21,097 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1081kB
19. 69.778 113.859 ↓ 1.1 21,097 1

HashAggregate (cost=1,229.46..1,428.69 rows=19,923 width=8) (actual time=89.895..113.859 rows=21,097 loops=1)

  • Group Key: ld_entities_projects_1.entity_id
20. 44.081 44.081 ↑ 1.0 37,113 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..44.081 rows=37,113 loops=1)

  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Rows Removed by Filter: 11575
21. 0.009 737.426 ↑ 5,325.0 2 1

Hash (cost=3,310.70..3,310.70 rows=10,650 width=43) (actual time=737.426..737.426 rows=2 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
22. 0.021 737.417 ↑ 5,325.0 2 1

Hash Left Join (cost=3,060.78..3,310.70 rows=10,650 width=43) (actual time=154.953..737.417 rows=2 loops=1)

  • Hash Cond: (original_compound.id = ld_compound_unlink.virtual_compound_id)
23. 0.022 737.391 ↑ 5,325.0 2 1

Hash Left Join (cost=3,009.15..3,124.63 rows=10,650 width=43) (actual time=154.934..737.391 rows=2 loops=1)

  • Hash Cond: (original_compound.data_source_id = original_source.id)
24. 31.673 737.346 ↑ 5,325.0 2 1

Hash Right Join (cost=3,008.06..3,012.71 rows=10,650 width=32) (actual time=154.895..737.346 rows=2 loops=1)

  • Hash Cond: (original_fragment.id = original_entity.id)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 21193
25. 0.016 0.016 ↓ 1.2 6 1

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

26. 25.992 154.587 ↑ 1.0 21,195 1

Hash (cost=2,741.83..2,741.83 rows=21,299 width=48) (actual time=154.587..154.587 rows=21,195 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1920kB
27. 48.616 128.595 ↑ 1.0 21,195 1

Hash Left Join (cost=863.29..2,741.83 rows=21,299 width=48) (actual time=55.159..128.595 rows=21,195 loops=1)

  • Hash Cond: (original_entity.id = original_compound.id)
28. 30.934 30.934 ↑ 1.0 21,195 1

Seq Scan on ld_entity original_entity (cost=0.00..1,609.63 rows=21,299 width=16) (actual time=5.982..30.934 rows=21,195 loops=1)

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

Hash (cost=598.13..598.13 rows=21,213 width=32) (actual time=49.045..49.045 rows=21,213 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1582kB
30. 24.799 24.799 ↑ 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.008..24.799 rows=21,213 loops=1)

31.          

SubPlan (forHash Right Join)

32. 84.791 551.070 ↓ 0.0 0 21,195

Append (cost=0.00..3.56 rows=2 width=4) (actual time=0.026..0.026 rows=0 loops=21,195)

33. 84.780 233.145 ↓ 0.0 0 21,195

Subquery Scan on tmp (cost=0.00..1.77 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=21,195)

  • Filter: (original_compound.jchem_structure_id = tmp.structure_id)
  • Rows Removed by Filter: 2
34. 105.975 148.365 ↑ 50.0 2 21,195

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.003..0.007 rows=2 loops=21,195)

35. 42.390 42.390 ↑ 1.0 1 21,195

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

36. 63.582 233.134 ↓ 0.0 0 21,194

Subquery Scan on tmp_1 (cost=0.00..1.77 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=21,194)

  • Filter: (original_fragment.jchem_structure_id = tmp_1.structure_id)
  • Rows Removed by Filter: 2
37. 127.164 169.552 ↑ 50.0 2 21,194

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.003..0.008 rows=2 loops=21,194)

38. 42.388 42.388 ↑ 1.0 1 21,194

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

39. 0.013 0.023 ↑ 1.0 4 1

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

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

41. 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
42. 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)

43. 0.036 0.036 ↓ 0.0 0 2

Index Scan using syn_compound_pkey on syn_compound (cost=0.29..0.39 rows=1 width=10) (actual time=0.018..0.018 rows=0 loops=2)

  • Index Cond: (id = ld_entity.id)