explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o5nO : ctd-2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 67,630.752 ↑ 1.0 50 1

Limit (cost=105,851,433.17..105,851,435.55 rows=50 width=687) (actual time=67,630.722..67,630.752 rows=50 loops=1)

2.          

Initplan (forLimit)

3. 4.589 4.589 ↑ 1.0 1 1

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

  • Index Cond: ((nm)::text = 'HeLa Cells'::text)
4. 0.025 67,630.748 ↑ 1,621,176.5 50 1

Unique (cost=105,851,424.60..109,701,718.74 rows=81,058,824 width=687) (actual time=67,630.721..67,630.748 rows=50 loops=1)

5. 3.499 67,630.723 ↑ 1,621,176.5 50 1

Sort (cost=105,851,424.60..106,054,071.66 rows=81,058,824 width=687) (actual time=67,630.720..67,630.723 rows=50 loops=1)

  • 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): (...)
  • Sort Method: quicksort Memory: 2203kB
6. 4.349 67,627.224 ↑ 43,886.7 1,847 1

WindowAgg (cost=46,835,610.63..70,545,316.65 rows=81,058,824 width=687) (actual time=67,624.867..67,627.224 rows=1,847 loops=1)

7. 12.534 67,622.875 ↑ 43,886.7 1,847 1

GroupAggregate (cost=46,835,610.63..63,047,375.43 rows=81,058,824 width=687) (actual time=67,610.315..67,622.875 rows=1,847 loops=1)

  • 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. 6.965 67,610.341 ↑ 42,730.0 1,897 1

Sort (cost=46,835,610.63..47,038,257.69 rows=81,058,824 width=687) (actual time=67,610.219..67,610.341 rows=1,897 loops=1)

  • Sort Key: associatedterm.nm_sort, ((((((((((associatedterm.nm)::text || '^'::text) || (o.cd)::text) || '^'::text) || (associatedterm.nm_html)::text) || '^'::text) || (associatedterm.acc_txt)::text) || '^'::text) || (associat (...)
  • Sort Method: quicksort Memory: 2122kB
9. 43.971 67,603.376 ↑ 42,730.0 1,897 1

Hash Join (cost=1,251,288.33..11,529,502.68 rows=81,058,824 width=687) (actual time=8,758.089..67,603.376 rows=1,897 loops=1)

  • Hash Cond: (ptr.ixn_id = ia.ixn_id)
10. 70.669 65,789.619 ↑ 196.7 231,899 1

Hash Left Join (cost=981,916.66..5,895,699.04 rows=45,617,070 width=634) (actual time=6,863.555..65,789.619 rows=231,899 loops=1)

  • Hash Cond: (ptr.taxon_id = taxonterm.id)
11. 3,217.689 64,596.048 ↑ 196.7 231,899 1

Hash Join (cost=749,695.63..5,431,363.15 rows=45,617,070 width=558) (actual time=5,733.562..64,596.048 rows=231,899 loops=1)

  • Hash Cond: (ptr.ixn_id = i.id)
12. 7,571.202 59,542.780 ↑ 1.7 27,638,624 1

Hash Join (cost=543,186.62..4,590,442.25 rows=47,530,984 width=199) (actual time=3,892.539..59,542.780 rows=27,638,624 loops=1)

  • Hash Cond: (ptr.reference_id = r.id)
13. 6,479.131 51,616.103 ↑ 1.7 28,796,953 1

Nested Loop (cost=464,443.89..3,850,670.19 rows=49,525,199 width=191) (actual time=3,536.067..51,616.103 rows=28,796,953 loops=1)

14. 476.866 6,040.729 ↑ 1.0 2,299,779 1

Hash Join (cost=464,443.33..717,770.23 rows=2,306,826 width=187) (actual time=3,536.028..6,040.729 rows=2,299,779 loops=1)

  • Hash Cond: (associatedterm.object_type_id = o.id)
15. 567.426 5,563.855 ↑ 1.0 2,299,779 1

Hash Join (cost=464,442.06..686,050.10 rows=2,306,826 width=184) (actual time=3,535.994..5,563.855 rows=2,299,779 loops=1)

  • Hash Cond: (pt.phenotype_id = phenotypeterm.id)
16. 1,043.433 3,159.786 ↑ 1.0 2,299,779 1

Hash Join (cost=232,221.03..419,226.68 rows=2,306,826 width=122) (actual time=1,693.520..3,159.786 rows=2,299,779 loops=1)

  • Hash Cond: (pt.term_id = associatedterm.id)
17. 936.890 936.890 ↑ 1.0 2,299,779 1

Seq Scan on phenotype_term pt (cost=0.00..152,403.26 rows=2,306,826 width=12) (actual time=507.222..936.890 rows=2,299,779 loops=1)

18. 601.677 1,179.463 ↑ 1.0 1,716,998 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 239079kB
19. 577.786 577.786 ↑ 1.0 1,716,998 1

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

20. 528.019 1,836.643 ↑ 1.0 1,716,998 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 179112kB
21. 1,308.624 1,308.624 ↑ 1.0 1,716,998 1

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

22. 0.004 0.008 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↑ 1.0 12 1

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

24. 39,096.243 39,096.243 ↓ 4.3 13 2,299,779

Index Scan using ix_phenotype_term_ref_ids on phenotype_term_reference ptr (cost=0.56..1.33 rows=3 width=20) (actual time=0.004..0.017 rows=13 loops=2,299,779)

  • Index Cond: ((phenotype_id = pt.phenotype_id) AND (term_id = pt.term_id))
25. 32.419 355.475 ↓ 1.0 174,849 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10076kB
26. 323.056 323.056 ↓ 1.0 174,849 1

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

27. 744.170 1,835.579 ↓ 1.0 1,486,372 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 589051kB
28. 1,091.409 1,091.409 ↓ 1.0 1,486,372 1

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

29. 550.671 1,122.902 ↑ 1.0 1,716,998 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 191906kB
30. 572.231 572.231 ↑ 1.0 1,716,998 1

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

31. 0.302 1,769.786 ↑ 184.0 1,140 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 2126kB
32. 11.418 1,769.484 ↑ 184.0 1,140 1

Hash Join (cost=259,045.65..266,749.09 rows=209,807 width=61) (actual time=1,699.305..1,769.484 rows=1,140 loops=1)

  • Hash Cond: (ia.anatomy_id = p.descendant_object_id)
33. 48.961 1,748.176 ↑ 1.0 209,807 1

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

  • Hash Cond: (ia.anatomy_id = anatomyterm.id)
34. 17.164 17.164 ↑ 1.0 209,807 1

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

35. 455.579 1,682.051 ↑ 1.0 1,716,998 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 170139kB
36. 1,226.472 1,226.472 ↑ 1.0 1,716,998 1

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

37. 0.011 9.890 ↑ 5.5 2 1

Hash (cost=26,824.49..26,824.49 rows=11 width=4) (actual time=9.890..9.890 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.010 9.879 ↑ 5.5 2 1

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

  • Group Key: p.descendant_object_id
39. 9.869 9.869 ↑ 1,484.0 6 1

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 time=9.778..9.869 rows=6 loops=1)

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

SubPlan (forWindowAgg)

41. 0.000 0.000 ↑ 1.0 1 1,847

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

42. 0.000 0.000 ↑ 1.0 1 1,847

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

43. 0.000 0.000 ↑ 1.0 1 1,847

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

44. 0.000 0.000 ↑ 1.0 1 1,847

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

Planning time : 27.891 ms
Execution time : 67,639.868 ms