explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4IQg : Before R-Group

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 0.531 ↑ 5.0 1 1

Nested Loop Left Join (cost=965.07..973.96 rows=5 width=8) (actual time=0.523..0.531 rows=1 loops=1)

  • Filter: ((syn_compound.id IS NULL) OR (syn_compound.reactant = 0))
2. 0.010 0.510 ↑ 5.0 1 1

Nested Loop (cost=964.78..971.95 rows=5 width=16) (actual time=0.505..0.510 rows=1 loops=1)

3. 0.015 0.481 ↑ 16.0 1 1

HashAggregate (cost=964.49..964.65 rows=16 width=40) (actual time=0.479..0.481 rows=1 loops=1)

  • Group Key: ld_entity_1.entity_id
4. 0.014 0.466 ↑ 8.0 2 1

Nested Loop (cost=703.62..964.45 rows=16 width=40) (actual time=0.444..0.466 rows=2 loops=1)

5. 0.010 0.396 ↑ 31.0 1 1

Nested Loop (cost=703.33..953.11 rows=31 width=48) (actual time=0.388..0.396 rows=1 loops=1)

6. 0.008 0.349 ↑ 31.0 1 1

Unique (cost=703.03..703.19 rows=31 width=32) (actual time=0.343..0.349 rows=1 loops=1)

7. 0.040 0.341 ↑ 31.0 1 1

Sort (cost=703.03..703.11 rows=31 width=32) (actual time=0.339..0.341 rows=1 loops=1)

  • Sort Key: (COALESCE(expanded_entity.entity_id, original_entity.entity_id))
  • Sort Method: quicksort Memory: 25kB
8. 0.009 0.301 ↑ 31.0 1 1

Nested Loop Left Join (cost=3.37..702.27 rows=31 width=32) (actual time=0.261..0.301 rows=1 loops=1)

9. 0.010 0.289 ↑ 31.0 1 1

Nested Loop Left Join (cost=3.08..688.25 rows=31 width=16) (actual time=0.251..0.289 rows=1 loops=1)

  • Join Filter: ((ld_compound_unlink.virtual_compound_id IS NULL) AND (original_source.alternate_id = 'pri'::text))
10. 0.008 0.256 ↑ 31.0 1 1

Nested Loop Left Join (cost=2.50..664.36 rows=31 width=43) (actual time=0.221..0.256 rows=1 loops=1)

11. 0.011 0.240 ↑ 31.0 1 1

Nested Loop Left Join (cost=2.35..659.03 rows=31 width=43) (actual time=0.208..0.240 rows=1 loops=1)

  • Join Filter: (original_compound.data_source_id = original_source.id)
  • Rows Removed by Join Filter: 1
12. 0.011 0.206 ↑ 31.0 1 1

Nested Loop (cost=2.35..656.40 rows=31 width=32) (actual time=0.177..0.206 rows=1 loops=1)

13. 0.018 0.180 ↑ 101.0 1 1

Nested Loop (cost=2.06..610.27 rows=101 width=24) (actual time=0.155..0.180 rows=1 loops=1)

14. 0.020 0.040 ↑ 50.0 2 1

HashAggregate (cost=1.77..2.77 rows=100 width=4) (actual time=0.034..0.040 rows=2 loops=1)

  • Group Key: unnest('{21172,17162}'::integer[])
15. 0.016 0.020 ↑ 50.0 2 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.012..0.020 rows=2 loops=1)

16. 0.004 0.004 ↑ 1.0 1 1

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

17. 0.122 0.122 ↓ 0.0 0 2

Index Scan using syn_compound_jchem_structure_id_idx on syn_compound original_compound (cost=0.29..6.06 rows=1 width=32) (actual time=0.060..0.061 rows=0 loops=2)

  • Index Cond: (jchem_structure_id = (unnest('{21172,17162}'::integer[])))
18. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pk_ld_entity on ld_entity original_entity (cost=0.29..0.46 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (id = original_compound.id)
  • Filter: ((entity_type <> 'SAMPLE'::text) AND (archived = 0))
19. 0.011 0.023 ↑ 2.0 2 1

Materialize (cost=0.00..1.06 rows=4 width=19) (actual time=0.017..0.023 rows=2 loops=1)

20. 0.012 0.012 ↑ 2.0 2 1

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

21. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ld_compound_unlink_virtual_compound_id_key on ld_compound_unlink (cost=0.15..0.17 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (virtual_compound_id = original_compound.id)
  • Heap Fetches: 0
22. 0.003 0.023 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.58..0.76 rows=1 width=24) (actual time=0.023..0.023 rows=0 loops=1)

23. 0.020 0.020 ↓ 0.0 0 1

Index Scan using canonical_structure_id_index on syn_compound expanded_compound (cost=0.29..0.40 rows=1 width=24) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (original_compound.canonical_structure_id = canonical_structure_id)
  • Filter: ((archived = 0) AND (reactant = 0) AND (original_compound.data_source_id <> data_source_id))
  • Rows Removed by Filter: 1
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using entities_projects_unq on ld_entities_projects ld_entities_projects_1 (cost=0.29..0.37 rows=2 width=8) (never executed)

  • Index Cond: (entity_id = expanded_compound.id)
  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Heap Fetches: 0
25. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pk_ld_entity on ld_entity expanded_entity (cost=0.29..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (id = expanded_compound.id)
26. 0.037 0.037 ↑ 1.0 1 1

Index Scan using ld_entity_entity_id_key on ld_entity ld_entity_1 (cost=0.29..8.05 rows=1 width=16) (actual time=0.037..0.037 rows=1 loops=1)

  • Index Cond: (entity_id = (COALESCE(expanded_entity.entity_id, original_entity.entity_id)))
27. 0.056 0.056 ↑ 1.0 2 1

Index Only Scan using entities_projects_unq on ld_entities_projects (cost=0.29..0.35 rows=2 width=8) (actual time=0.050..0.056 rows=2 loops=1)

  • Index Cond: (entity_id = ld_entity_1.id)
  • Filter: (project_id = ANY ('{0,1,2,3}'::bigint[]))
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
28. 0.019 0.019 ↑ 1.0 1 1

Index Scan using ld_entity_entity_id_key on ld_entity (cost=0.29..0.46 rows=1 width=16) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (entity_id = ld_entity_1.entity_id)
  • Filter: ((entity_type = ANY ('{COMPOUND,FRAGMENT}'::text[])) AND (archived = 0))
29. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: (id = ld_entity.id)