explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BQZS : ctd1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=105,851,433.17..105,851,435.55 rows=50 width=687) (actual rows= loops=)

2.          

Initplan (forLimit)

3. 0.000 0.000 ↓ 0.0

Index Scan using ix_term_nm on term (cost=0.55..8.57 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((nm)::text = 'HeLa Cells'::text)
4. 0.000 0.000 ↓ 0.0

Unique (cost=105,851,424.60..109,701,718.74 rows=81,058,824 width=687) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=105,851,424.60..106,054,071.66 rows=81,058,824 width=687) (actual rows= loops=)

  • Sort Key: associatedterm.nm_sort, ((((((((((associatedterm.nm)::text || '^'::text) || (o.cd)::text) || '^'::text) || (associatedterm.nm_html)::text) || '^'::text) || (associatedterm.acc_txt)::text) || '^'::text) || (associatedterm.acc_db_cd): (...)
6. 0.000 0.000 ↓ 0.0

WindowAgg (cost=46,835,610.63..70,545,316.65 rows=81,058,824 width=687) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=46,835,610.63..63,047,375.43 rows=81,058,824 width=687) (actual rows= loops=)

  • Group Key: associatedterm.nm_sort, ((((((((((associatedterm.nm)::text || '^'::text) || (o.cd)::text) || '^'::text) || (associatedterm.nm_html)::text) || '^'::text) || (associatedterm.acc_txt)::text) || '^'::text) || (associatedter (...)
8. 0.000 0.000 ↓ 0.0

Sort (cost=46,835,610.63..47,038,257.69 rows=81,058,824 width=687) (actual rows= loops=)

  • Sort Key: associatedterm.nm_sort, ((((((((((associatedterm.nm)::text || '^'::text) || (o.cd)::text) || '^'::text) || (associatedterm.nm_html)::text) || '^'::text) || (associatedterm.acc_txt)::text) || '^'::text) || (associat (...)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,251,288.33..11,529,502.68 rows=81,058,824 width=687) (actual rows= loops=)

  • Hash Cond: (ptr.ixn_id = ia.ixn_id)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=981,916.66..5,895,699.04 rows=45,617,070 width=634) (actual rows= loops=)

  • Hash Cond: (ptr.taxon_id = taxonterm.id)
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=749,695.63..5,431,363.15 rows=45,617,070 width=558) (actual rows= loops=)

  • Hash Cond: (ptr.ixn_id = i.id)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=543,186.62..4,590,442.25 rows=47,530,984 width=199) (actual rows= loops=)

  • Hash Cond: (ptr.reference_id = r.id)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=464,443.89..3,850,670.19 rows=49,525,199 width=191) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=464,443.33..717,770.23 rows=2,306,826 width=187) (actual rows= loops=)

  • Hash Cond: (associatedterm.object_type_id = o.id)
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=464,442.06..686,050.10 rows=2,306,826 width=184) (actual rows= loops=)

  • Hash Cond: (pt.phenotype_id = phenotypeterm.id)
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=232,221.03..419,226.68 rows=2,306,826 width=122) (actual rows= loops=)

  • Hash Cond: (pt.term_id = associatedterm.id)
17. 0.000 0.000 ↓ 0.0

Seq Scan on phenotype_term pt (cost=0.00..152,403.26 rows=2,306,826 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=210,732.68..210,732.68 rows=1,719,068 width=110) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on term associatedterm (cost=0.00..210,732.68 rows=1,719,068 width=110) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=210,732.68..210,732.68 rows=1,719,068 width=62) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on term phenotypeterm (cost=0.00..210,732.68 rows=1,719,068 width=62) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=1.12..1.12 rows=12 width=11) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on object_type o (cost=0.00..1.12 rows=12 width=11) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan using ix_phenotype_term_ref_ids on phenotype_term_reference ptr (cost=0.56..1.33 rows=3 width=20) (actual rows= loops=)

  • Index Cond: ((phenotype_id = pt.phenotype_id) AND (term_id = pt.term_id))
25. 0.000 0.000 ↓ 0.0

Hash (cost=76,560.10..76,560.10 rows=174,610 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on reference r (cost=0.00..76,560.10 rows=174,610 width=12) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=187,929.56..187,929.56 rows=1,486,356 width=359) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on ixn i (cost=0.00..187,929.56 rows=1,486,356 width=359) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=210,732.68..210,732.68 rows=1,719,068 width=84) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on term taxonterm (cost=0.00..210,732.68 rows=1,719,068 width=84) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=266,749.09..266,749.09 rows=209,807 width=61) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=259,045.65..266,749.09 rows=209,807 width=61) (actual rows= loops=)

  • Hash Cond: (ia.anatomy_id = p.descendant_object_id)
33. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=232,221.03..239,137.20 rows=209,807 width=65) (actual rows= loops=)

  • Hash Cond: (ia.anatomy_id = anatomyterm.id)
34. 0.000 0.000 ↓ 0.0

Seq Scan on ixn_anatomy ia (cost=0.00..3,769.07 rows=209,807 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=210,732.68..210,732.68 rows=1,719,068 width=57) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on term anatomyterm (cost=0.00..210,732.68 rows=1,719,068 width=57) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=26,824.49..26,824.49 rows=11 width=4) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=26,824.38..26,824.49 rows=11 width=4) (actual rows= loops=)

  • Group Key: p.descendant_object_id
39. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_dag_path_anc_term on dag_path p (cost=0.56..26,802.12 rows=8,904 width=4) (actual rows= loops=)

  • Index Cond: (ancestor_object_id = $4)
40.          

SubPlan (forWindowAgg)

41. 0.000 0.000 ↓ 0.0

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

42. 0.000 0.000 ↓ 0.0

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

43. 0.000 0.000 ↓ 0.0

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

44. 0.000 0.000 ↓ 0.0

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