explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qqiN

Settings
# exclusive inclusive rows x rows loops node
1. 1,635.221 133,364.626 ↓ 0.0 0 1

Update on explanation ex0 (cost=37,028.00..39,824.04 rows=211 width=289) (actual time=133,364.625..133,364.626 rows=0 loops=1)

2.          

CTE event_articles

3. 5.527 287.417 ↓ 197.2 42,389 1

Subquery Scan on x (cost=34,025.46..35,420.88 rows=215 width=16) (actual time=254.055..287.417 rows=42,389 loops=1)

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

WindowAgg (cost=34,025.46..34,884.18 rows=42,936 width=28) (actual time=254.033..281.890 rows=42,936 loops=1)

5. 32.529 260.625 ↑ 1.0 42,936 1

Sort (cost=34,025.46..34,132.80 rows=42,936 width=20) (actual time=254.015..260.625 rows=42,936 loops=1)

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

Hash Join (cost=1,297.06..30,721.55 rows=42,936 width=20) (actual time=15.960..228.096 rows=42,936 loops=1)

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

Seq Scan on data d (cost=0.00..28,406.55 rows=156,955 width=12) (actual time=0.018..162.881 rows=162,207 loops=1)

8. 7.874 15.539 ↑ 1.0 42,936 1

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

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

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

10.          

CTE explanation_data_from_article

11. 26.740 366.238 ↓ 90.6 40,864 1

Nested Loop (cost=0.29..1,601.20 rows=451 width=16) (actual time=295.371..366.238 rows=40,864 loops=1)

12. 297.109 297.109 ↓ 197.2 42,389 1

CTE Scan on event_articles (cost=0.00..4.30 rows=215 width=16) (actual time=254.058..297.109 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.41 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=42,389)

  • Index Cond: (article_id = event_articles.article_id)
14. 189.084 131,729.405 ↓ 199.6 42,119 1

Bitmap Heap Scan on explanation ex0 (cost=5.93..2,801.96 rows=211 width=289) (actual time=379.754..131,729.405 rows=42,119 loops=1)

  • Recheck Cond: (event_id IS NULL)
  • Heap Blocks: exact=1,743
15. 2.684 2.684 ↓ 199.6 42,119 1

Bitmap Index Scan on idx__explanation__event_id (cost=0.00..5.87 rows=211 width=0) (actual time=2.684..2.684 rows=42,119 loops=1)

  • Index Cond: (event_id IS NULL)
16.          

SubPlan (for Bitmap Heap Scan)

17. 131,537.637 131,537.637 ↑ 2.0 1 42,119

CTE Scan on explanation_data_from_article eda (cost=0.00..10.15 rows=2 width=8) (actual time=1.612..3.123 rows=1 loops=42,119)

  • Filter: (explanation_id = ex0.id)
  • Rows Removed by Filter: 40,863
Planning time : 1.260 ms
Execution time : 133,679.855 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint event_id__fk 306.290 ms 40864 0.007 ms