explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 99UW : test2

Settings
# exclusive inclusive rows x rows loops node
1. 1.008 44,487.859 ↑ 73.2 1,068 1

Hash Join (cost=565.04..8,153.16 rows=78,125 width=640) (actual time=44,486.138..44,487.859 rows=1,068 loops=1)

  • Hash Cond: (srcl.changetranstypeid = (tat.actiontypeid)::integer)
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
2. 0.574 44,486.822 ↑ 14.6 1,068 1

Merge Join (cost=542.53..865.03 rows=15,625 width=640) (actual time=44,486.095..44,486.822 rows=1,068 loops=1)

  • Merge Cond: (((tst.styletypeid)::integer) = srcl.styletype)
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
3. 0.010 0.020 ↑ 1,000.0 1 1

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual time=0.017..0.020 rows=1 loops=1)

  • Sort Key: ((tst.styletypeid)::integer)
  • Sort Method: quicksort Memory: 25kB
4. 0.010 0.010 ↑ 1,000.0 1 1

Function Scan on regexp_split_to_table tst (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.007..0.010 rows=1 loops=1)

5. 1.341 44,486.228 ↑ 2.9 1,069 1

Sort (cost=482.70..490.51 rows=3,125 width=640) (actual time=44,486.074..44,486.228 rows=1,069 loops=1)

  • Sort Key: srcl.styletype
  • Sort Method: quicksort Memory: 990kB
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
6. 0.809 44,484.887 ↑ 2.9 1,069 1

Merge Join (cost=232.55..301.30 rows=3,125 width=640) (actual time=44,483.808..44,484.887 rows=1,069 loops=1)

  • Merge Cond: (srcl.workflowid = ((tw.workflowid)::uuid))
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
7. 1.502 44,483.840 ↓ 1.7 1,070 1

Sort (cost=172.72..174.28 rows=625 width=640) (actual time=44,483.699..44,483.840 rows=1,070 loops=1)

  • Sort Key: srcl.workflowid
  • Sort Method: quicksort Memory: 991kB
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
8. 1.858 44,482.338 ↓ 1.7 1,070 1

Hash Join (cost=79.94..143.69 rows=625 width=640) (actual time=44,480.487..44,482.338 rows=1,070 loops=1)

  • Hash Cond: ((td.divisionid)::uuid = srcl.divisionid)
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
9. 0.009 0.009 ↑ 1,000.0 1 1

Function Scan on regexp_split_to_table td (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.006..0.009 rows=1 loops=1)

10. 1.085 44,480.471 ↓ 8.6 1,070 1

Hash (cost=78.38..78.38 rows=125 width=640) (actual time=44,480.469..44,480.471 rows=1,070 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 598kB
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
11. 4.145 44,479.386 ↓ 8.6 1,070 1

Hash Join (cost=57.13..78.38 rows=125 width=640) (actual time=44,475.180..44,479.386 rows=1,070 loops=1)

  • Hash Cond: ((tcb.teamid)::uuid = srcl.changeuserid)
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
12. 1.517 1.517 ↓ 1.3 1,282 1

Function Scan on regexp_split_to_table tcb (cost=0.00..10.00 rows=1,000 width=32) (actual time=1.305..1.517 rows=1,282 loops=1)

13. 1.104 44,473.724 ↓ 42.8 1,070 1

Hash (cost=56.81..56.81 rows=25 width=640) (actual time=44,473.722..44,473.724 rows=1,070 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 598kB
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
14. 16.117 44,472.620 ↓ 42.8 1,070 1

Hash Join (cost=40.31..56.81 rows=25 width=640) (actual time=44,465.824..44,472.620 rows=1,070 loops=1)

  • Hash Cond: ((tsc.stylecategoryid)::uuid = srcl.stylecategory)
  • Buffers: shared hit=7441241 read=252972, temp read=118270 written=118154
15. 0.009 0.009 ↑ 1,000.0 1 1

Function Scan on regexp_split_to_table tsc (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.006..0.009 rows=1 loops=1)

16. 14.577 44,456.494 ↓ 1,963.8 9,819 1

Hash (cost=40.25..40.25 rows=5 width=640) (actual time=44,456.491..44,456.494 rows=9,819 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 4 (originally 1) Memory Usage: 4033kB
  • Buffers: shared hit=7441241 read=252972, temp read=117315 written=117737
17. 44,441.917 44,441.917 ↓ 1,963.8 9,819 1

Function Scan on fnx_style_report_change_log_select srcl (cost=0.25..40.25 rows=5 width=640) (actual time=43,993.555..44,441.917 rows=9,819 loops=1)

  • Filter: ((workflowid <> '50000000-0000-0000-0000-000000000005'::uuid) AND ((styleno)::text ~~* '%%'::text) AND (((changedate + ('00:00'::cstring)::interval))::date >= '2020-01-02'::date) AND (((changedate + ('00:00'::cstring)::interval))::date <= '2020-02-04'::date))
  • Rows Removed by Filter: 393954
  • Buffers: shared hit=7441241 read=252972, temp read=117315 written=117202
18. 0.158 0.238 ↓ 1.1 1,086 1

Sort (cost=59.83..62.33 rows=1,000 width=32) (actual time=0.100..0.238 rows=1,086 loops=1)

  • Sort Key: ((tw.workflowid)::uuid)
  • Sort Method: quicksort Memory: 27kB
19. 0.080 0.080 ↑ 47.6 21 1

Function Scan on regexp_split_to_table tw (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.049..0.080 rows=21 loops=1)

20. 0.008 0.029 ↑ 166.7 6 1

Hash (cost=10.00..10.00 rows=1,000 width=32) (actual time=0.027..0.029 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.021 0.021 ↑ 166.7 6 1

Function Scan on regexp_split_to_table tat (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.018..0.021 rows=6 loops=1)