explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oqR3 : ctdtom1

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 101.600 ↑ 30.7 237 1

Subquery Scan on test (cost=956,680.70..956,989.59 rows=7,268 width=328) (actual time=101.484..101.600 rows=237 loops=1)

2. 0.072 101.563 ↑ 30.7 237 1

Unique (cost=956,680.70..956,916.91 rows=7,268 width=218) (actual time=101.481..101.563 rows=237 loops=1)

3. 1.058 101.491 ↑ 30.7 237 1

Sort (cost=956,680.70..956,698.87 rows=7,268 width=218) (actual time=101.480..101.491 rows=237 loops=1)

  • Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, ((SubPlan 1)), ((SubPlan 2)), i.ixn_prose_txt, ((SubPlan 3)), ((SubPlan 4)), i.id, ( (...)
  • Sort Method: quicksort Memory: 165kB
4. 1.891 100.433 ↑ 30.7 237 1

GroupAggregate (cost=452,428.44..956,214.56 rows=7,268 width=218) (actual time=95.523..100.433 rows=237 loops=1)

  • Group Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5))
5. 2.019 95.461 ↑ 14.3 510 1

Sort (cost=452,428.44..452,446.61 rows=7,268 width=218) (actual time=95.427..95.461 rows=510 loops=1)

  • Sort Key: associatedterm.nm, associatedterm.acc_txt, (COALESCE(associatedterm.secondary_nm, ''::character varying)), phenotypeterm.nm, phenotypeterm.acc_txt, i.id, i.ixn_prose_txt, ((SubPlan 5))
  • Sort Method: quicksort Memory: 306kB
6. 0.792 93.442 ↑ 14.3 510 1

Nested Loop (cost=20,087.06..451,962.29 rows=7,268 width=218) (actual time=40.699..93.442 rows=510 loops=1)

  • Join Filter: (ptr.ixn_id = iaxn.ixn_id)
7. 0.194 47.324 ↑ 18.0 239 1

Nested Loop Left Join (cost=20,086.64..206,072.31 rows=4,293 width=212) (actual time=40.529..47.324 rows=239 loops=1)

8. 0.061 46.652 ↑ 18.0 239 1

Nested Loop (cost=20,086.21..171,868.77 rows=4,293 width=186) (actual time=40.503..46.652 rows=239 loops=1)

9. 0.104 45.396 ↑ 18.0 239 1

Nested Loop (cost=20,085.78..137,665.22 rows=4,293 width=156) (actual time=40.482..45.396 rows=239 loops=1)

10. 0.151 44.814 ↑ 18.0 239 1

Nested Loop (cost=20,085.36..103,461.68 rows=4,293 width=108) (actual time=40.455..44.814 rows=239 loops=1)

11. 0.305 42.751 ↑ 18.9 239 1

Nested Loop (cost=20,084.93..73,349.34 rows=4,506 width=28) (actual time=40.430..42.751 rows=239 loops=1)

12. 0.056 41.490 ↑ 19.8 239 1

Nested Loop (cost=20,084.51..48,394.32 rows=4,730 width=24) (actual time=40.405..41.490 rows=239 loops=1)

13. 0.026 0.514 ↓ 10.3 31 1

HashAggregate (cost=7,354.43..7,354.46 rows=3 width=4) (actual time=0.509..0.514 rows=31 loops=1)

  • Group Key: p.descendant_object_id
14. 0.488 0.488 ↑ 25.6 93 1

Index Only Scan using ix_dag_path_anc_term on dag_path p (cost=0.56..7,348.48 rows=2,380 width=4) (actual time=0.053..0.488 rows=93 loops=1)

  • Index Cond: (ancestor_object_id = 1053851)
  • Heap Fetches: 93
15. 1.178 40.920 ↓ 4.0 8 31

Bitmap Heap Scan on phenotype_term_reference ptr (cost=12,730.08..13,679.93 rows=2 width=20) (actual time=1.287..1.320 rows=8 loops=31)

  • Recheck Cond: ((term_id = p.descendant_object_id) AND (term_object_type_id = 2))
  • Filter: ((source_cd)::text = 'C'::text)
  • Rows Removed by Filter: 46
  • Heap Blocks: exact=1278
16. 0.145 39.742 ↓ 0.0 0 31

BitmapAnd (cost=12,730.08..12,730.08 rows=242 width=0) (actual time=1.282..1.282 rows=0 loops=31)

17. 0.372 0.372 ↑ 163.6 54 31

Bitmap Index Scan on ix_phenotype_term_ref_term_id (cost=0.00..166.71 rows=8,836 width=0) (actual time=0.012..0.012 rows=54 loops=31)

  • Index Cond: (term_id = p.descendant_object_id)
18. 39.225 39.225 ↑ 1.1 630,202 1

Bitmap Index Scan on ix_phenotype_term_ref_object_type_id (cost=0.00..12,561.73 rows=680,172 width=0) (actual time=39.225..39.225 rows=630,202 loops=1)

  • Index Cond: (term_object_type_id = 2)
19. 0.956 0.956 ↑ 1.0 1 239

Index Scan using reference_pk on reference r (cost=0.42..5.27 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=239)

  • Index Cond: (id = ptr.reference_id)
20. 1.912 1.912 ↑ 1.0 1 239

Index Scan using ixn_pk on ixn i (cost=0.43..6.67 rows=1 width=80) (actual time=0.008..0.008 rows=1 loops=239)

  • Index Cond: (id = ptr.ixn_id)
21. 0.478 0.478 ↑ 1.0 1 239

Index Scan using term_pk on term associatedterm (cost=0.43..7.96 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=239)

  • Index Cond: (id = ptr.term_id)
22. 1.195 1.195 ↑ 1.0 1 239

Index Scan using term_pk on term phenotypeterm (cost=0.43..7.96 rows=1 width=34) (actual time=0.004..0.005 rows=1 loops=239)

  • Index Cond: (id = ptr.phenotype_id)
23. 0.478 0.478 ↑ 1.0 1 239

Index Scan using term_pk on term taxonterm (cost=0.43..7.96 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=239)

  • Index Cond: (ptr.taxon_id = id)
24. 0.956 0.956 ↑ 1.0 2 239

Index Scan using ix_ixn_axn_ixn_id on ixn_axn iaxn (cost=0.42..0.46 rows=2 width=14) (actual time=0.004..0.004 rows=2 loops=239)

  • Index Cond: (ixn_id = i.id)
25.          

SubPlan (forNested Loop)

26. 9.180 44.370 ↑ 1.0 1 510

Aggregate (cost=33.54..33.55 rows=1 width=22) (actual time=0.087..0.087 rows=1 loops=510)

27. 4.436 35.190 ↓ 10.0 20 510

Nested Loop (cost=0.99..33.53 rows=2 width=22) (actual time=0.007..0.069 rows=20 loops=510)

28. 10.710 10.710 ↓ 10.0 20 510

Index Scan using ix_phenotype_term_ref_ids on phenotype_term_reference ptr2 (cost=0.56..16.62 rows=2 width=4) (actual time=0.005..0.021 rows=20 loops=510)

  • Index Cond: ((ptr.phenotype_id = phenotype_id) AND (ptr.term_id = term_id))
  • Filter: ((source_cd)::text = 'I'::text)
  • Rows Removed by Filter: 6
29. 20.044 20.044 ↑ 1.0 1 10,022

Index Scan using term_pk on term t (cost=0.43..8.45 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=10,022)

  • Index Cond: (id = ptr2.via_term_id)
30.          

SubPlan (forGroupAggregate)

31. 0.000 0.000 ↑ 1.0 1 237

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)

32. 0.237 3.081 ↑ 1.0 1 237

Aggregate (cost=35.68..35.69 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=237)

33. 0.474 2.844 ↑ 2.0 1 237

Sort (cost=35.64..35.64 rows=2 width=34) (actual time=0.011..0.012 rows=1 loops=237)

  • Sort Key: ia.level_seq
  • Sort Method: quicksort Memory: 25kB
34. 0.095 2.370 ↑ 2.0 1 237

Nested Loop Left Join (cost=1.27..35.63 rows=2 width=34) (actual time=0.009..0.010 rows=1 loops=237)

35. 0.237 1.659 ↑ 2.0 1 237

Nested Loop Left Join (cost=0.85..18.72 rows=2 width=8) (actual time=0.006..0.007 rows=1 loops=237)

  • Join Filter: (i2.id = ia.ixn_id)
36. 0.474 0.474 ↑ 1.0 1 237

Index Only Scan using ixn_pk on ixn i2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=237)

  • Index Cond: (id = i.id)
  • Heap Fetches: 237
37. 0.948 0.948 ↑ 2.0 1 237

Index Scan using ixn_anatomy_pk on ixn_anatomy ia (cost=0.42..10.25 rows=2 width=12) (actual time=0.004..0.004 rows=1 loops=237)

  • Index Cond: (ixn_id = i.id)
38. 0.616 0.616 ↑ 1.0 1 308

Index Scan using term_pk on term anatomyterm (cost=0.43..8.45 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=308)

  • Index Cond: (ia.anatomy_id = id)
39. 0.000 0.000 ↑ 1.0 1 237

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)

40. 0.000 0.000 ↑ 1.0 1 237

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=237)