explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hSOD

Settings
# exclusive inclusive rows x rows loops node
1. 618.359 1,008.149 ↓ 0.0 0 1

Update on explanation ex0 (cost=37,097.87..40,026.13 rows=452 width=297) (actual time=1,008.149..1,008.149 rows=0 loops=1)

2.          

CTE event_articles

3. 6.322 217.850 ↓ 197.2 42,389 1

Subquery Scan on x (cost=34,097.67..35,493.09 rows=215 width=16) (actual time=181.099..217.850 rows=42,389 loops=1)

  • Filter: (x.pos = 1)
  • Rows Removed by Filter: 547
4. 23.686 211.528 ↑ 1.0 42,936 1

WindowAgg (cost=34,097.67..34,956.39 rows=42,936 width=28) (actual time=181.090..211.528 rows=42,936 loops=1)

5. 30.858 187.842 ↑ 1.0 42,936 1

Sort (cost=34,097.67..34,205.01 rows=42,936 width=20) (actual time=181.081..187.842 rows=42,936 loops=1)

  • Sort Key: ev.event_id, d.rank DESC
  • Sort Method: external sort Disk: 1,432kB
6. 41.415 156.984 ↑ 1.0 42,936 1

Hash Join (cost=1,297.06..30,793.77 rows=42,936 width=20) (actual time=11.789..156.984 rows=42,936 loops=1)

  • Hash Cond: (d.id = ev.article_id)
7. 103.915 103.915 ↑ 1.0 162,207 1

Seq Scan on data d (cost=0.00..28,459.07 rows=162,207 width=12) (actual time=0.006..103.915 rows=162,207 loops=1)

8. 5.890 11.654 ↑ 1.0 42,936 1

Hash (cost=760.36..760.36 rows=42,936 width=16) (actual time=11.654..11.654 rows=42,936 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,525kB
9. 5.764 5.764 ↑ 1.0 42,936 1

Seq Scan on evidence ev (cost=0.00..760.36 rows=42,936 width=16) (actual time=0.007..5.764 rows=42,936 loops=1)

10.          

CTE explanation_data_from_article

11. 23.927 294.268 ↓ 90.4 40,864 1

Nested Loop (cost=0.29..1,604.49 rows=452 width=16) (actual time=221.965..294.268 rows=40,864 loops=1)

12. 227.952 227.952 ↓ 197.2 42,389 1

CTE Scan on event_articles (cost=0.00..4.30 rows=215 width=16) (actual time=181.102..227.952 rows=42,389 loops=1)

13. 42.389 42.389 ↑ 2.0 1 42,389

Index Scan using idx__explanation__article_id on explanation ex (cost=0.29..7.42 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=42,389)

  • Index Cond: (article_id = event_articles.article_id)
14. 0.000 389.790 ↓ 90.4 40,864 1

Nested Loop (cost=0.29..2,928.55 rows=452 width=297) (actual time=221.988..389.790 rows=40,864 loops=1)

15. 308.346 308.346 ↓ 90.4 40,864 1

CTE Scan on explanation_data_from_article eda (cost=0.00..9.04 rows=452 width=56) (actual time=221.976..308.346 rows=40,864 loops=1)

16. 81.728 81.728 ↑ 1.0 1 40,864

Index Scan using explanation_pkey on explanation ex0 (cost=0.29..6.45 rows=1 width=245) (actual time=0.001..0.002 rows=1 loops=40,864)

  • Index Cond: (id = eda.explanation_id)
  • Filter: (event_id IS NULL)
Planning time : 0.612 ms
Execution time : 1,891.566 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint event_id__fk 874.068 ms 40864 0.021 ms