explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upu4D : old

Settings
# exclusive inclusive rows x rows loops node
1. 8.439 111,696.237 ↑ 18.5 906 1

HashAggregate (cost=9,038,397.46..9,038,565.44 rows=16,798 width=40) (actual time=111,695.933..111,696.237 rows=906 loops=1)

  • Group Key: changes_after_publication.duty_line_id, changes_after_publication.event_type
2.          

CTE changes_after_publication

3. 8.466 111,675.311 ↑ 9.9 16,987 1

Append (cost=1,372.42..9,034,197.98 rows=167,979 width=40) (actual time=172.690..111,675.311 rows=16,987 loops=1)

4. 28.212 951.939 ↑ 9.1 16,944 1

Nested Loop Left Join (cost=1,372.42..1,510,618.62 rows=154,320 width=56) (actual time=172.689..951.939 rows=16,944 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: 18082
5. 10.762 619.527 ↑ 9.1 16,900 1

Nested Loop Left Join (cost=1,371.98..97,423.43 rows=154,320 width=32) (actual time=171.802..619.527 rows=16,900 loops=1)

  • Join Filter: (br_publish.br_id = dty_duty_line.customer_br_id)
6. 593.982 599.301 ↑ 14.3 676 1

Bitmap Heap Scan on dty_duty_line (cost=1,371.98..94,442.54 rows=9,645 width=24) (actual time=171.764..599.301 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: 33113
  • Heap Blocks: exact=11120
7. 5.319 5.319 ↓ 1.2 34,045 1

Bitmap Index Scan on dty_duty_line_87959b (cost=0.00..1,369.57 rows=28,935 width=0) (actual time=5.319..5.319 rows=34,045 loops=1)

  • Index Cond: (customer_br_id = 10000108)
8. 5.240 9.464 ↓ 1.6 25 676

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

9. 4.224 4.224 ↓ 1.6 25 1

Seq Scan on br_publish (cost=0.00..666.05 rows=16 width=24) (actual time=0.024..4.224 rows=25 loops=1)

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

Index Scan using index_aggregate_identifier_bigint on dty_duty_line_event (cost=0.44..9.11 rows=3 width=65) (actual time=0.015..0.018 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. 38.449 110,714.906 ↑ 317.7 43 1

Nested Loop (cost=3,647,211.02..7,521,899.57 rows=13,659 width=57) (actual time=107,043.407..110,714.906 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.payload ->> 'dutyDate'::text))::timestamp without time zone <= br_publish_1.publish_date))
  • Rows Removed by Join Filter: 8057
12. 3,688.187 110,671.597 ↑ 71.1 324 1

Hash Join (cost=3,647,211.02..7,510,989.04 rows=23,050 width=2,447) (actual time=107,027.196..110,671.597 rows=324 loops=1)

  • Hash Cond: ((deleted.duty_line_id)::text = (all_events.duty_line_id)::text)
13. 29,048.248 29,048.248 ↑ 18.9 163 1

Seq Scan on dty_duty_line_event deleted (cost=0.00..2,345,645.39 rows=3,076 width=1,204) (actual time=5,433.036..29,048.248 rows=163 loops=1)

  • Filter: (((payload_type)::text ~~ '%DutyLineDeleted'::text) AND (((payload ->> 'customerBrId'::text))::bigint = 10000108) AND "overlaps"('2018-10-31 23:00:00+00'::timestamp with time zone, '2018-11-30 23:00:00+00'::timestamp with time zone, (((payload ->> 'dutyDatetimeBegin'::text))::timestamp without time zone)::timestamp with time zone, (((payload ->> 'dutyDatetimeEnd'::text))::timestamp without time zone)::timestamp with time zone))
  • Rows Removed by Filter: 10759925
14. 40,729.680 77,935.162 ↓ 1.0 10,106,715 1

Hash (cost=2,013,133.27..2,013,133.27 rows=9,683,420 width=1,252) (actual time=77,935.162..77,935.162 rows=10,106,715 loops=1)

  • Buckets: 1024 Batches: 4096 Memory Usage: 3451kB
15. 37,205.482 37,205.482 ↓ 1.0 10,106,715 1

Seq Scan on dty_duty_line_event all_events (cost=0.00..2,013,133.27 rows=9,683,420 width=1,252) (actual time=0.031..37,205.482 rows=10,106,715 loops=1)

  • Filter: ((payload_type)::text !~~ '%DutyLinePeriod%'::text)
  • Rows Removed by Filter: 653373
16. 2.250 4.860 ↓ 1.6 25 324

Materialize (cost=0.00..666.13 rows=16 width=24) (actual time=0.000..0.015 rows=25 loops=324)

17. 2.610 2.610 ↓ 1.6 25 1

Seq Scan on br_publish br_publish_1 (cost=0.00..666.05 rows=16 width=24) (actual time=0.009..2.610 rows=25 loops=1)

  • Filter: (br_id = 10000108)
  • Rows Removed by Filter: 31985
18. 111,687.798 111,687.798 ↑ 9.9 16,987 1

CTE Scan on changes_after_publication (cost=0.00..3,359.58 rows=167,979 width=40) (actual time=172.693..111,687.798 rows=16,987 loops=1)