explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xy4E

Settings
# exclusive inclusive rows x rows loops node
1. 42.442 56,443.290 ↑ 3.2 7,157 1

Unique (cost=10,814,658.10..10,816,364.49 rows=22,752 width=524) (actual time=56,354.751..56,443.290 rows=7,157 loops=1)

2.          

CTE changes_after_publication

3. 73.680 56,111.414 ↑ 1.3 175,594 1

Append (cost=1,788.71..10,682,546.50 rows=227,519 width=25) (actual time=10.779..56,111.414 rows=175,594 loops=1)

4. 112.571 815.922 ↑ 1.3 175,551 1

Nested Loop Left Join (cost=1,788.71..4,198,529.62 rows=223,873 width=25) (actual time=10.778..815.922 rows=175,551 loops=1)

  • Join Filter: ((dty_duty_line_event."timestamp" >= br_publish.published_at) AND (dty_duty_line.duty_date <= br_publish.publish_date))
  • Rows Removed by Join Filter: 181220
5. 89.306 177.007 ↑ 1.3 175,448 1

Nested Loop Left Join (cost=1,788.15..128,126.72 rows=223,873 width=32) (actual time=10.754..177.007 rows=175,448 loops=1)

  • Join Filter: (br_publish.br_id = dty_duty_line.customer_br_id)
6. 35.996 40.465 ↑ 2.0 6,748 1

Bitmap Heap Scan on dty_duty_line (cost=1,788.15..124,067.18 rows=13,169 width=24) (actual time=10.742..40.465 rows=6,748 loops=1)

  • Recheck Cond: (customer_br_id = 10000108)
  • Filter: "overlaps"('2018-11-01 00:00:00+01'::timestamp with time zone, '2019-12-01 00:00:00+01'::timestamp with time zone, duty_datetime_begin, duty_datetime_end)
  • Rows Removed by Filter: 26716
  • Heap Blocks: exact=11941
7. 4.469 4.469 ↑ 1.1 35,006 1

Bitmap Index Scan on dty_duty_line_87959b (cost=0.00..1,784.86 rows=39,506 width=0) (actual time=4.469..4.469 rows=35,006 loops=1)

  • Index Cond: (customer_br_id = 10000108)
8. 44.243 47.236 ↓ 1.5 26 6,748

Materialize (cost=0.00..701.49 rows=17 width=24) (actual time=0.000..0.007 rows=26 loops=6,748)

9. 2.993 2.993 ↓ 1.5 26 1

Seq Scan on br_publish (cost=0.00..701.40 rows=17 width=24) (actual time=0.006..2.993 rows=26 loops=1)

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 33612
10. 526.344 526.344 ↑ 3.0 1 175,448

Index Scan using index_aggregate_identifier_bigint on dty_duty_line_event (cost=0.56..18.14 rows=3 width=34) (actual time=0.002..0.003 rows=1 loops=175,448)

  • Index Cond: ((duty_line_id)::bigint = dty_duty_line.duty_line_id)
  • Filter: (((payload_type)::text !~~ '%DutyLinePeriod%'::text) AND ((payload_type)::text <> 'DutyLineDeleted'::text))
11. 2.891 55,221.812 ↑ 84.8 43 1

Nested Loop (cost=6,381,831.93..6,481,741.70 rows=3,646 width=26) (actual time=54,636.491..55,221.812 rows=43 loops=1)

  • Join Filter: ((deleted."timestamp" >= br_publish_1.published_at) AND (all_events."timestamp" >= br_publish_1.published_at) AND (all_events.duty_date <= br_publish_1.publish_date))
  • Rows Removed by Join Filter: 8381
12. 610.194 55,214.709 ↑ 17.9 324 1

Hash Join (cost=6,381,831.93..6,479,053.08 rows=5,791 width=58) (actual time=54,625.865..55,214.709 rows=324 loops=1)

  • Hash Cond: ((deleted.duty_line_id)::text = (all_events.duty_line_id)::text)
13. 4.946 4.946 ↑ 4.0 163 1

Index Only Scan using dty_duty_line_event_deleted_idx on dty_duty_line_event deleted (cost=0.56..227.19 rows=646 width=25) (actual time=0.061..4.946 rows=163 loops=1)

  • Index Cond: ((customer_br_id = 10000108) AND (duty_date_time_begin < '2018-12-01 00:00:00+01'::timestamp with time zone) AND (duty_date_time_end > '2018-11-01 00:00:00+01'::timestamp with time zone) AND (payload_type = 'DutyLineDeleted'::text))
  • Heap Fetches: 0
14. 6,441.303 54,599.569 ↓ 1.0 11,064,995 1

Hash (cost=6,149,873.20..6,149,873.20 rows=10,895,614 width=42) (actual time=54,599.569..54,599.569 rows=11,064,995 loops=1)

  • Buckets: 8192 Batches: 256 Memory Usage: 3422kB
15. 48,158.266 48,158.266 ↓ 1.0 11,064,995 1

Seq Scan on dty_duty_line_event all_events (cost=0.00..6,149,873.20 rows=10,895,614 width=42) (actual time=0.006..48,158.266 rows=11,064,995 loops=1)

  • Filter: ((payload_type)::text !~~ '%DutyLinePeriod%'::text)
  • Rows Removed by Filter: 653373
16. 1.904 4.212 ↓ 1.5 26 324

Materialize (cost=0.00..701.49 rows=17 width=24) (actual time=0.000..0.013 rows=26 loops=324)

17. 2.308 2.308 ↓ 1.5 26 1

Seq Scan on br_publish br_publish_1 (cost=0.00..701.40 rows=17 width=24) (actual time=0.009..2.308 rows=26 loops=1)

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 33612
18. 178.061 56,400.848 ↑ 1.3 175,594 1

Sort (cost=132,111.60..132,680.39 rows=227,519 width=524) (actual time=56,354.750..56,400.848 rows=175,594 loops=1)

  • Sort Key: changes_after_publication.duty_line_id, changes_after_publication.event_type
  • Sort Method: external merge Disk: 3096kB
19. 56,222.787 56,222.787 ↑ 1.3 175,594 1

CTE Scan on changes_after_publication (cost=0.00..4,550.38 rows=227,519 width=524) (actual time=10.781..56,222.787 rows=175,594 loops=1)