explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nt7Y : With New Table

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 51.472 ↑ 2.0 1 1

Nested Loop Left Join (cost=583.57..586.13 rows=2 width=8) (actual time=51.467..51.472 rows=1 loops=1)

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

Nested Loop (cost=583.29..585.33 rows=2 width=16) (actual time=51.453..51.457 rows=1 loops=1)

3. 0.010 51.433 ↑ 5.0 1 1

HashAggregate (cost=582.99..583.04 rows=5 width=40) (actual time=51.432..51.433 rows=1 loops=1)

  • Group Key: ld_entity_1.entity_id
4. 0.010 51.423 ↑ 2.5 2 1

Nested Loop (cost=505.35..582.98 rows=5 width=40) (actual time=51.409..51.423 rows=2 loops=1)

5. 0.009 51.365 ↑ 9.0 1 1

Nested Loop (cost=505.06..579.69 rows=9 width=48) (actual time=51.360..51.365 rows=1 loops=1)

6. 0.005 51.332 ↑ 9.0 1 1

Unique (cost=504.76..504.81 rows=9 width=32) (actual time=51.329..51.332 rows=1 loops=1)

7. 0.092 51.327 ↑ 9.0 1 1

Sort (cost=504.76..504.79 rows=9 width=32) (actual time=51.326..51.327 rows=1 loops=1)

  • Sort Key: (COALESCE(expanded_entity.entity_id, original_entity.entity_id))
  • Sort Method: quicksort Memory: 25kB
8. 0.008 51.235 ↑ 9.0 1 1

Nested Loop Left Join (cost=434.37..504.62 rows=9 width=32) (actual time=51.203..51.235 rows=1 loops=1)

9. 0.009 51.223 ↑ 9.0 1 1

Nested Loop Left Join (cost=434.08..500.55 rows=9 width=16) (actual time=51.193..51.223 rows=1 loops=1)

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

Nested Loop Left Join (cost=433.50..493.62 rows=9 width=43) (actual time=51.168..51.196 rows=1 loops=1)

11. 0.024 51.179 ↑ 9.0 1 1

Hash Left Join (cost=433.35..492.07 rows=9 width=43) (actual time=51.153..51.179 rows=1 loops=1)

  • Hash Cond: (original_compound.data_source_id = original_source.id)
12. 0.010 51.116 ↑ 9.0 1 1

Nested Loop (cost=432.26..490.88 rows=9 width=32) (actual time=51.092..51.116 rows=1 loops=1)

13. 0.015 51.088 ↑ 15.5 2 1

Nested Loop (cost=431.97..478.83 rows=31 width=24) (actual time=51.073..51.088 rows=2 loops=1)

14. 0.018 51.033 ↑ 50.5 2 1

HashAggregate (cost=431.68..432.69 rows=101 width=8) (actual time=51.030..51.033 rows=2 loops=1)

  • Group Key: ld_entity_structure.entity_id
15. 22.817 51.015 ↑ 50.5 2 1

Hash Join (cost=2.77..431.43 rows=101 width=8) (actual time=38.408..51.015 rows=2 loops=1)

  • Hash Cond: (ld_entity_structure.jchem_structure_id = (unnest('{21172,17162}'::integer[])))
16. 28.176 28.176 ↓ 1.0 21,218 1

Seq Scan on ld_entity_structure (cost=0.00..348.11 rows=21,211 width=16) (actual time=0.012..28.176 rows=21,218 loops=1)

17. 0.009 0.022 ↑ 50.0 2 1

Hash (cost=1.52..1.52 rows=100 width=4) (actual time=0.022..0.022 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.010 0.013 ↑ 50.0 2 1

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

19. 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)

20. 0.040 0.040 ↑ 1.0 1 2

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

  • Index Cond: (id = ld_entity_structure.entity_id)
  • Filter: ((entity_type <> 'SAMPLE'::text) AND (archived = 0))
21. 0.018 0.018 ↓ 0.0 0 2

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

  • Index Cond: (id = original_entity.id)
22. 0.023 0.039 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.016 0.016 ↑ 1.0 4 1

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

24. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

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

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

26. 0.015 0.015 ↓ 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.015..0.015 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
27. 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
28. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Index Cond: (id = expanded_compound.id)
29. 0.024 0.024 ↑ 1.0 1 1

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

  • Index Cond: (entity_id = (COALESCE(expanded_entity.entity_id, original_entity.entity_id)))
30. 0.048 0.048 ↑ 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.045..0.048 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
31. 0.014 0.014 ↑ 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.014..0.014 rows=1 loops=1)

  • Index Cond: (entity_id = ld_entity_1.entity_id)
  • Filter: ((entity_type = ANY ('{COMPOUND,FRAGMENT}'::text[])) AND (archived = 0))
32. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (id = ld_entity.id)