explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y2E : changes after publication

Settings
# exclusive inclusive rows x rows loops node
1. 4.014 22,839.195 ↑ 23.0 899 1

Unique (cost=8,566,173.03..8,567,724.66 rows=20,689 width=524) (actual time=22,831.942..22,839.195 rows=899 loops=1)

2.          

CTE changes_after_publication

3. 6.893 22,816.248 ↑ 12.2 16,985 1

Append (cost=1,847.16..8,494,975.47 rows=206,885 width=56) (actual time=5.656..22,816.248 rows=16,985 loops=1)

4. 20.101 92.514 ↑ 12.2 16,942 1

Nested Loop Left Join (cost=1,847.16..2,781,264.01 rows=206,880 width=56) (actual time=5.656..92.514 rows=16,942 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: 17921
5. 8.649 38.613 ↑ 12.2 16,900 1

Nested Loop Left Join (cost=1,846.72..125,195.22 rows=206,880 width=32) (actual time=5.641..38.613 rows=16,900 loops=1)

  • Join Filter: (br_publish.br_id = dty_duty_line.customer_br_id)
6. 19.938 23.880 ↑ 19.1 676 1

Bitmap Heap Scan on dty_duty_line (cost=1,846.72..121,426.12 rows=12,930 width=24) (actual time=5.632..23.880 rows=676 loops=1)

  • Recheck Cond: (customer_br_id = 10000108)
  • Filter: "overlaps"('2018-10-31 23:00:00+00'::timestamp with time zone, '2018-11-30 23:00:00+00'::timestamp with time zone, duty_datetime_begin, duty_datetime_end)
  • Rows Removed by Filter: 33367
  • Heap Blocks: exact=10965
7. 3.942 3.942 ↑ 1.1 34,043 1

Bitmap Index Scan on dty_duty_line_87959b (cost=0.00..1,843.48 rows=38,790 width=0) (actual time=3.942..3.942 rows=34,043 loops=1)

  • Index Cond: (customer_br_id = 10000108)
8. 3.629 6.084 ↓ 1.6 25 676

Materialize (cost=0.00..665.94 rows=16 width=24) (actual time=0.000..0.009 rows=25 loops=676)

9. 2.455 2.455 ↓ 1.6 25 1

Seq Scan on br_publish (cost=0.00..665.86 rows=16 width=24) (actual time=0.005..2.455 rows=25 loops=1)

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 31884
10. 33.800 33.800 ↑ 3.0 1 16,900

Index Scan using index_aggregate_identifier_bigint on dty_duty_line_event (cost=0.44..12.79 rows=3 width=65) (actual time=0.002..0.002 rows=1 loops=16,900)

  • 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.600 22,716.841 ↓ 8.6 43 1

Nested Loop (cost=8.59..5,711,642.60 rows=5 width=57) (actual time=22,716.451..22,716.841 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: 8432
12. 2.316 2.316 ↓ 1.6 25 1

Seq Scan on br_publish br_publish_1 (cost=0.00..665.86 rows=16 width=24) (actual time=0.007..2.316 rows=25 loops=1)

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 31884
13. 1.879 22,711.925 ↓ 42.4 339 25

Materialize (cost=8.59..5,710,974.18 rows=8 width=89) (actual time=55.053..908.477 rows=339 loops=25)

14. 2,625.257 22,710.046 ↓ 42.4 339 1

Hash Join (cost=8.59..5,710,974.14 rows=8 width=89) (actual time=1,376.313..22,710.046 rows=339 loops=1)

  • Hash Cond: ((all_events.duty_line_id)::text = (deleted.duty_line_id)::text)
15. 20,084.429 20,084.429 ↑ 1.0 10,714,943 1

Seq Scan on dty_duty_line_event all_events (cost=0.00..5,670,784.43 rows=10,714,943 width=73) (actual time=0.002..20,084.429 rows=10,714,943 loops=1)

16. 0.065 0.360 ↓ 163.0 163 1

Hash (cost=8.58..8.58 rows=1 width=25) (actual time=0.360..0.360 rows=163 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.295 0.295 ↓ 163.0 163 1

Index Only Scan using dty_duty_line_event_deleted_idx on dty_duty_line_event deleted (cost=0.55..8.58 rows=1 width=25) (actual time=0.052..0.295 rows=163 loops=1)

  • Index Cond: ((customer_br_id = 10000108) AND (duty_date_time_begin < '2018-11-30 23:00:00+00'::timestamp with time zone) AND (duty_date_time_end > '2018-10-31 23:00:00+00'::timestamp with time zone) AND (payload_type = 'DutyLineDeleted'::text))
  • Heap Fetches: 163
18. 9.619 22,835.181 ↑ 12.2 16,985 1

Sort (cost=71,197.56..71,714.77 rows=206,885 width=524) (actual time=22,831.940..22,835.181 rows=16,985 loops=1)

  • Sort Key: changes_after_publication.duty_line_id, changes_after_publication.event_type
  • Sort Method: quicksort Memory: 1575kB
19. 22,825.562 22,825.562 ↑ 12.2 16,985 1

CTE Scan on changes_after_publication (cost=0.00..4,137.70 rows=206,885 width=524) (actual time=5.658..22,825.562 rows=16,985 loops=1)