explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k8Ap : cookie_dealer_map

Settings
# exclusive inclusive rows x rows loops node
1. 25.213 410,313.639 ↓ 11,887.0 11,887 1

Aggregate (cost=7,013,221.58..7,013,221.63 rows=1 width=99) (actual time=410,281.202..410,313.639 rows=11,887 loops=1)

  • Buffers: shared hit=44325177 read=4715841 dirtied=5557 written=264
2.          

CTE web_responses_sub

3. 1,238.637 77,766.361 ↓ 1.0 10,520,899 1

Append (cost=0..5,259,193.23 rows=10,503,619 width=25) (actual time=0.029..77,766.361 rows=10,520,899 loops=1)

  • Buffers: shared read=4221376 written=142
4. 17,205.259 17,205.259 ↑ 1.0 2,514,435 1

Seq Scan on web_responses_2019_04 web_responses_2019_04 (cost=0..1,185,460.86 rows=2,517,678 width=25) (actual time=0.028..17,205.259 rows=2,514,435 loops=1)

  • Filter: ((web_responses_2019_04.page_classification)::text = ANY ('{vdp,VDP,srp,SRP}'::text[]))
  • Buffers: shared read=973031 written=142
5. 59,322.465 59,322.465 ↓ 1.0 8,006,464 1

Seq Scan on web_responses_2019_03 web_responses_2019_03 (cost=0..3,968,696.18 rows=7,985,941 width=25) (actual time=1.619..59,322.465 rows=8,006,464 loops=1)

  • Filter: ((web_responses_2019_03.page_classification)::text = ANY ('{vdp,VDP,srp,SRP}'::text[]))
  • Buffers: shared read=3248345
6. 125.342 410,288.426 ↓ 40,975.0 40,975 1

Sort (cost=1,754,028.34..1,754,028.35 rows=1 width=71) (actual time=410,281.163..410,288.426 rows=40,975 loops=1)

  • Sort Key: web_responses_sub.property_id, narrative_raw.clarivoy_id, narrative_raw.md5_email
  • Sort Method: quicksort Memory: 4738kB
  • Buffers: shared hit=44325177 read=4715841 dirtied=5557 written=264
7. 115.346 410,163.084 ↓ 40,975.0 40,975 1

Nested Loop (cost=0.99..1,754,028.33 rows=1 width=71) (actual time=510.806..410,163.084 rows=40,975 loops=1)

  • Buffers: shared hit=44325168 read=4715841 dirtied=5557 written=264
8. 7,707.943 396,957.818 ↓ 40,906.0 40,906 1

Nested Loop (cost=0.56..1,754,024.85 rows=1 width=71) (actual time=507.375..396,957.818 rows=40,906 loops=1)

  • Buffers: shared hit=43946474 read=4700478 written=261
9. 84,143.804 84,143.804 ↓ 50.1 10,520,899 1

CTE Scan on web_responses_sub web_responses_sub (cost=0..262,590.48 rows=210,072 width=56) (actual time=0.033..84,143.804 rows=10,520,899 loops=1)

  • Filter: ((web_responses_sub.page_classification)::text = ANY ('{vdp,VDP,srp,SRP}'::text[]))
  • Buffers: shared read=4221376 written=142
10. 305,106.071 305,106.071 ↓ 0.0 0 10,520,899

Index Scan using narrative_raw_clarivoy_id_idx on narrative_raw narrative_raw (cost=0.56..7.09 rows=1 width=31) (actual time=0.029..0.029 rows=0 loops=10,520,899)

  • Index Cond: (narrative_raw.clarivoy_id = web_responses_sub.td_client_id)
  • Filter: ((narrative_raw.md5_email IS NOT NULL) AND (narrative_raw.load_date >= (now() - '1 day'::interval)))
  • Buffers: shared hit=43946474 read=479102 written=119
11. 13,089.920 13,089.920 ↑ 1.0 1 40,906

Index Scan using cookie_dealer_map_td_client_id_property_id_idx on cookie_dealer_map cookie_dealer_map (cost=0.43..3.47 rows=1 width=49) (actual time=0.244..0.32 rows=1 loops=40,906)

  • Index Cond: (narrative_raw.clarivoy_id = cookie_dealer_map.td_client_id)
  • Filter: (encode(narrative_raw.md5_email, 'HEX'::text) = (cookie_dealer_map.email_md5)::text)
  • Buffers: shared hit=378694 read=15363 dirtied=5557 written=3