explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SDM4 : new query - worse result

Settings
# exclusive inclusive rows x rows loops node
1. 4.006 76,365.699 ↑ 23.0 899 1

Unique (cost=8,566,174.07..8,567,725.71 rows=20,689 width=524) (actual time=76,358.466..76,365.699 rows=899 loops=1)

2.          

CTE changes_after_publication

3. 6.853 76,342.230 ↑ 12.2 16,985 1

Append (cost=1,847.16..8,494,976.51 rows=206,885 width=56) (actual time=5.612..76,342.230 rows=16,985 loops=1)

4. 16.761 88.723 ↑ 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.612..88.723 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.402 38.162 ↑ 12.2 16,900 1

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

  • Join Filter: (br_publish.br_id = dty_duty_line.customer_br_id)
6. 19.827 23.676 ↑ 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.586..23.676 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.849 3.849 ↑ 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.849..3.849 rows=34,043 loops=1)

  • Index Cond: (customer_br_id = 10000108)
8. 3.624 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.460 2.460 ↓ 1.6 25 1

Seq Scan on br_publish (cost=0.00..665.86 rows=16 width=24) (actual time=0.004..2.460 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.643 76,246.654 ↓ 8.6 43 1

Nested Loop (cost=8.59..5,711,643.65 rows=5 width=57) (actual time=76,246.268..76,246.654 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.311 2.311 ↓ 1.6 25 1

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

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 31884
13. 1.861 76,241.700 ↓ 42.4 339 25

Materialize (cost=8.59..5,710,975.22 rows=8 width=89) (actual time=506.198..3,049.668 rows=339 loops=25)

14. 2,755.877 76,239.839 ↓ 42.4 339 1

Hash Join (cost=8.59..5,710,975.18 rows=8 width=89) (actual time=12,654.929..76,239.839 rows=339 loops=1)

  • Hash Cond: ((all_events.duty_line_id)::text = (deleted.duty_line_id)::text)
15. 73,483.573 73,483.573 ↑ 1.0 10,714,946 1

Seq Scan on dty_duty_line_event all_events (cost=0.00..5,670,785.46 rows=10,714,946 width=73) (actual time=0.022..73,483.573 rows=10,714,946 loops=1)

16. 0.060 0.389 ↓ 163.0 163 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.329 0.329 ↓ 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.053..0.329 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. 10.207 76,361.693 ↑ 12.2 16,985 1

Sort (cost=71,197.56..71,714.77 rows=206,885 width=524) (actual time=76,358.463..76,361.693 rows=16,985 loops=1)

  • Sort Key: changes_after_publication.duty_line_id, changes_after_publication.event_type
  • Sort Method: quicksort Memory: 1575kB
19. 76,351.486 76,351.486 ↑ 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.614..76,351.486 rows=16,985 loops=1)