explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IrPs

Settings
# exclusive inclusive rows x rows loops node
1. 9.955 733,023.336 ↓ 410.5 6,978 1

Group (cost=20,487.45..20,488.56 rows=17 width=4,016) (actual time=733,009.144..733,023.336 rows=6,978 loops=1)

  • Group Key: ci.contentitemid, ci.contentid, ci.created, ci.modified, cif.name, ci.duration, ci.title, ci.contenttext, m.mediatype, m.mediaformat, m.name, m.internalurl, m.externalurl, li.text, (string_agg(DISTINCT (''::text || (cit.termid)::text), ','::text)), ci.stateid, ci.sortorder, m.thumbnailpath, ci.description, m.waveformdata, m.cdnurl, m.mediaid, m.cdnthumbnailurl, ci.link, ci.copyrighturl
2.          

Initplan (for Group)

3. 0.002 0.002 ↑ 1.0 1 1

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

4. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

5. 71.472 733,013.379 ↓ 410.5 6,978 1

Sort (cost=20,487.43..20,487.47 rows=17 width=4,016) (actual time=733,009.139..733,013.379 rows=6,978 loops=1)

  • Sort Key: ci.contentitemid, ci.contentid, ci.created, ci.modified, cif.name, ci.duration, ci.title, ci.contenttext, m.mediatype, m.mediaformat, m.name, m.internalurl, m.externalurl, li.text, (string_agg(DISTINCT (''::text || (cit.termid)::text), ','::text)), ci.stateid, ci.sortorder, m.thumbnailpath, ci.description, m.waveformdata, m.cdnurl, m.mediaid, m.cdnthumbnailurl, ci.link, ci.copyrighturl
  • Sort Method: external merge Disk: 3552kB
6. 7,275.257 732,941.907 ↓ 410.5 6,978 1

Nested Loop (cost=3,643.10..20,487.08 rows=17 width=4,016) (actual time=267.600..732,941.907 rows=6,978 loops=1)

  • Join Filter: (ci.contentid = c.contentid)
  • Rows Removed by Join Filter: 15407424
7. 20,783.124 673,736.374 ↓ 6,978.0 6,978 1

Nested Loop Left Join (cost=3,543.10..20,268.28 rows=1 width=4,024) (actual time=257.084..673,736.374 rows=6,978 loops=1)

  • Join Filter: (ci.contentitemid = cii.contentitemid)
  • Rows Removed by Join Filter: 52914174
8. 4,823.320 220,756.864 ↓ 6,978.0 6,978 1

Nested Loop Left Join (cost=500.00..7,890.54 rows=1 width=3,992) (actual time=146.805..220,756.864 rows=6,978 loops=1)

  • Join Filter: (m.contentitemid = ci.contentitemid)
  • Rows Removed by Join Filter: 7988061
9. 28.620 12,351.661 ↓ 6,977.0 6,977 1

Nested Loop Left Join (cost=400.00..7,780.05 rows=1 width=1,760) (actual time=115.355..12,351.661 rows=6,977 loops=1)

  • Join Filter: (li.contentitemid = ci.contentitemid)
  • Rows Removed by Join Filter: 13954
10. 97.274 12,316.064 ↓ 6,977.0 6,977 1

Nested Loop (cost=300.00..7,653.86 rows=1 width=1,728) (actual time=114.594..12,316.064 rows=6,977 loops=1)

  • Join Filter: (ci.contentitemformatid = cif.contentitemformatid)
  • Rows Removed by Join Filter: 167448
11. 6,241.140 12,162.974 ↓ 6,977.0 6,977 1

Nested Loop (cost=200.00..7,537.53 rows=1 width=1,220) (actual time=113.828..12,162.974 rows=6,977 loops=1)

  • Join Filter: (cp2c.contentid = ci.contentid)
  • Rows Removed by Join Filter: 15704999
12. 10.418 10.418 ↓ 15.6 2,072 1

Foreign Scan (cost=100.00..308.26 rows=133 width=8) (actual time=2.997..10.418 rows=2,072 loops=1)

  • Relations: (public.contentcomponent_to_content cp2c) INNER JOIN (public.contentcomponent cp)
13. 5,812.628 5,911.416 ↓ 3,791.5 7,583 2,072

Materialize (cost=100.00..7,225.28 rows=2 width=1,212) (actual time=0.002..2.853 rows=7,583 loops=2,072)

14. 98.788 98.788 ↓ 3,791.5 7,583 1

Foreign Scan on contentitem ci (cost=100.00..7,225.27 rows=2 width=1,212) (actual time=2.955..98.788 rows=7,583 loops=1)

  • Filter: ((created > $0) OR ((modified IS NOT NULL) AND (modified > $1)) OR (alternatives: SubPlan 5 or hashed SubPlan 8))
15.          

SubPlan (for Foreign Scan)

16. 0.000 0.000 ↓ 0.0 0

Foreign Scan on media m_1 (cost=100.02..169.93 rows=9 width=0) (never executed)

17.          

Initplan (for Foreign Scan)

18. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Foreign Scan on media m_2 (cost=100.02..176.66 rows=1,412 width=8) (never executed)

21.          

Initplan (for Foreign Scan)

22. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

24. 55.816 55.816 ↑ 6.0 25 6,977

Foreign Scan on contentitemformat cif (cost=100.00..114.47 rows=149 width=524) (actual time=0.001..0.008 rows=25 loops=6,977)

25. 6.977 6.977 ↑ 3.0 2 6,977

Foreign Scan on layoutinfo li (cost=100.00..126.12 rows=6 width=40) (actual time=0.001..0.001 rows=2 loops=6,977)

26. 203,581.883 203,581.883 ↓ 1,145.0 1,145 6,977

Foreign Scan on media m (cost=100.00..110.47 rows=1 width=2,240) (actual time=2.401..29.179 rows=1,145 loops=6,977)

27. 177,164.442 432,196.386 ↓ 37.9 7,584 6,978

GroupAggregate (cost=3,043.10..12,373.25 rows=200 width=40) (actual time=0.396..61.937 rows=7,584 loops=6,978)

  • Group Key: cii.contentitemid
28. 48,615.726 255,031.944 ↑ 39.5 8,503 6,978

Merge Left Join (cost=3,043.10..8,176.51 rows=335,539 width=16) (actual time=0.377..36.548 rows=8,503 loops=6,978)

  • Merge Cond: (cii.contentitemid = t2ci.contentitemid)
29. 199,849.920 199,849.920 ↓ 3.0 7,584 6,978

Foreign Scan on contentitem cii (cost=100.00..193.92 rows=2,560 width=8) (actual time=0.369..28.640 rows=7,584 loops=6,978)

30. 6,546.349 6,566.298 ↑ 11.0 2,378 6,978

Sort (cost=2,943.10..3,008.64 rows=26,214 width=16) (actual time=0.004..0.941 rows=2,378 loops=6,978)

  • Sort Key: t2ci.contentitemid
  • Sort Method: quicksort Memory: 208kB
31. 3.102 19.949 ↑ 11.0 2,378 1

Merge Left Join (cost=615.80..1,019.25 rows=26,214 width=16) (actual time=14.804..19.949 rows=2,378 loops=1)

  • Merge Cond: (t2ci.termid = cit.termid)
32. 2.214 9.978 ↓ 1.2 2,378 1

Sort (cost=284.08..289.20 rows=2,048 width=16) (actual time=9.015..9.978 rows=2,378 loops=1)

  • Sort Key: t2ci.termid
  • Sort Method: quicksort Memory: 208kB
33. 7.764 7.764 ↓ 1.2 2,378 1

Foreign Scan on term_to_contentitem t2ci (cost=100.00..171.44 rows=2,048 width=16) (actual time=0.778..7.764 rows=2,378 loops=1)

34. 1.933 6.869 ↓ 1.2 2,974 1

Sort (cost=331.72..338.12 rows=2,560 width=8) (actual time=5.722..6.869 rows=2,974 loops=1)

  • Sort Key: cit.termid
  • Sort Method: quicksort Memory: 112kB
35. 4.936 4.936 ↑ 1.9 1,354 1

Foreign Scan on term cit (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.794..4.936 rows=1,354 loops=1)

36. 51,930.276 51,930.276 ↑ 1.2 2,209 6,978

Foreign Scan on content c (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.306..7.442 rows=2,209 loops=6,978)

Planning time : 6.449 ms