explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HQKg

Settings

Optimization(s) for this plan:

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

GroupAggregate (cost=133,925,294.12..134,081,650.47 rows=19,800 width=560) (actual rows= loops=)

  • Group Key: unique_docs.doc_id, amazon.id
2.          

CTE amazon

3. 0.000 0.000 ↓ 0.0

Seq Scan on product (cost=0.00..2,079.73 rows=29,351 width=40) (actual rows= loops=)

  • Filter: (((key)::text !~~ '%http%'::text) AND ((key)::text !~~ '%.com%'::text))
4.          

CTE non_amazon

5. 0.000 0.000 ↓ 0.0

Seq Scan on product product_1 (cost=0.00..2,079.73 rows=24,631 width=40) (actual rows= loops=)

  • Filter: (((key)::text ~~ '%http%'::text) OR ((key)::text ~~ '%.com%'::text))
6.          

CTE unique_docs

7. 0.000 0.000 ↓ 0.0

WindowAgg (cost=5,330.55..5,880.51 rows=27,498 width=56) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=5,330.55..5,399.30 rows=27,498 width=48) (actual rows= loops=)

  • Sort Key: dotdash_meta.doc_id, dotdash_meta.published DESC
9. 0.000 0.000 ↓ 0.0

Seq Scan on dotdash_meta (cost=0.00..3,302.98 rows=27,498 width=48) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=133,915,254.15..133,941,280.54 rows=10,410,557 width=556) (actual rows= loops=)

  • Sort Key: unique_docs.doc_id, amazon.id
11. 0.000 0.000 ↓ 0.0

Merge Join (cost=124,387,105.55..124,909,153.86 rows=10,410,557 width=556) (actual rows= loops=)

  • Merge Cond: ((unique_docs.url_hash)::text = (tl.url_hash)::text)
12. 0.000 0.000 ↓ 0.0

Sort (cost=623.57..623.91 rows=137 width=1,032) (actual rows= loops=)

  • Sort Key: unique_docs.url_hash
13. 0.000 0.000 ↓ 0.0

CTE Scan on unique_docs (cost=0.00..618.70 rows=137 width=1,032) (actual rows= loops=)

  • Filter: (r_num = 1)
14. 0.000 0.000 ↓ 0.0

Materialize (cost=124,386,481.98..124,766,429.31 rows=15,197,893 width=556) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Unique (cost=124,386,481.98..124,576,455.65 rows=15,197,893 width=556) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=124,386,481.98..124,424,476.72 rows=15,197,893 width=556) (actual rows= loops=)

  • Sort Key: tl.url_hash, tl.href, tl.snapshot_link_id, amazon.id
17. 0.000 0.000 ↓ 0.0

Append (cost=0.00..111,197,411.36 rows=15,197,893 width=556) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..110,955,432.91 rows=15,153,628 width=109) (actual rows= loops=)

  • Join Filter: (tl.href ~~ (('%'::text || (amazon.key)::text) || '%'::text))
19. 0.000 0.000 ↓ 0.0

CTE Scan on amazon (cost=0.00..587.02 rows=29,351 width=520) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..5,342.87 rows=103,258 width=105) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on themis_link tl (cost=0.00..3,111.58 rows=103,258 width=105) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,117.31..14,010.06 rows=44,265 width=109) (actual rows= loops=)

  • Hash Cond: ((non_amazon.key)::text = tl_1.href)
23. 0.000 0.000 ↓ 0.0

CTE Scan on non_amazon (cost=0.00..492.62 rows=24,631 width=520) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=3,111.58..3,111.58 rows=103,258 width=105) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on themis_link tl_1 (cost=0.00..3,111.58 rows=103,258 width=105) (actual rows= loops=)