explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6mO8

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 10,371.092 ↓ 0.0 0 1

WindowAgg (cost=45,183,348.07..45,183,348.09 rows=1 width=3,330) (actual time=10,371.092..10,371.092 rows=0 loops=1)

2. 0.000 10,371.086 ↓ 0.0 0 1

Sort (cost=45,183,348.07..45,183,348.08 rows=1 width=3,322) (actual time=10,371.085..10,371.086 rows=0 loops=1)

  • Sort Key: a.rno
  • Sort Method: quicksort Memory: 25kB
3. 436.926 10,408.060 ↓ 0.0 0 1

Gather (cost=1,292,119.84..45,183,348.06 rows=1 width=3,322) (actual time=10,371.061..10,408.060 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 205.410 9,971.134 ↓ 0.0 0 3

Hash Join (cost=1,291,119.84..45,182,347.96 rows=1 width=3,322) (actual time=9,971.134..9,971.134 rows=0 loops=3)

  • Hash Cond: (lower((a.newbegbates)::text) = lower((b.c_pagealias)::text))
5. 273.239 9,755.816 ↓ 333,333.0 333,333 3

Merge Left Join (cost=1,290,649.27..45,181,873.14 rows=1 width=3,322) (actual time=8,899.289..9,755.816 rows=333,333 loops=3)

  • Merge Cond: ((lower((a.newbegbates)::text)) = (lower((c.c_docdisplayid)::text)))
  • Filter: (c.c_datastoresid IS NULL)
6. 6,069.199 6,993.693 ↑ 1.3 333,333 3

Sort (cost=1,126,904.41..1,127,954.10 rows=419,873 width=3,322) (actual time=6,410.377..6,993.693 rows=333,333 loops=3)

  • Sort Key: (lower((a.newbegbates)::text))
  • Sort Method: external merge Disk: 463608kB
  • Worker 0: Sort Method: external merge Disk: 472600kB
  • Worker 1: Sort Method: external merge Disk: 487800kB
7. 924.494 924.494 ↑ 1.3 333,333 3

Parallel Seq Scan on temp_dat_10032019095701_72056 a (cost=0.00..383,292.73 rows=419,873 width=3,322) (actual time=0.056..924.494 rows=333,333 loops=3)

8. 0.015 2,488.884 ↑ 17,134.1 61 3

Materialize (cost=163,744.86..168,970.77 rows=1,045,183 width=20) (actual time=2,488.844..2,488.884 rows=61 loops=3)

9. 1,532.740 2,488.869 ↑ 17,134.1 61 3

Sort (cost=163,744.86..166,357.82 rows=1,045,183 width=20) (actual time=2,488.835..2,488.869 rows=61 loops=3)

  • Sort Key: (lower((c.c_docdisplayid)::text))
  • Sort Method: external merge Disk: 48152kB
  • Worker 0: Sort Method: external merge Disk: 48152kB
  • Worker 1: Sort Method: external merge Disk: 48152kB
10. 518.925 956.129 ↑ 1.0 1,044,906 3

Result (cost=0.00..46,694.75 rows=1,045,183 width=20) (actual time=0.045..956.129 rows=1,044,906 loops=3)

11. 90.342 437.204 ↑ 1.0 1,044,906 3

Append (cost=0.00..46,694.75 rows=1,045,183 width=20) (actual time=0.036..437.204 rows=1,044,906 loops=3)

12. 0.013 0.013 ↓ 0.0 0 3

Seq Scan on t_e20so1_document_datastore_1 c (cost=0.00..10.60 rows=60 width=12) (actual time=0.013..0.013 rows=0 loops=3)

13. 0.435 0.435 ↑ 1.0 997 3

Seq Scan on t_e20so1_document_datastore_11 c_1 (cost=0.00..37.97 rows=997 width=31) (actual time=0.021..0.435 rows=997 loops=3)

14. 346.374 346.374 ↑ 1.0 1,043,880 3

Seq Scan on t_e20so1_document_datastore_12 c_2 (cost=0.00..41,407.67 rows=1,044,067 width=20) (actual time=0.018..346.374 rows=1,043,880 loops=3)

15. 0.036 0.036 ↑ 1.0 29 3

Seq Scan on t_e20so1_document_datastore_13 c_3 (cost=0.00..2.29 rows=29 width=20) (actual time=0.028..0.036 rows=29 loops=3)

16. 0.004 0.004 ↓ 0.0 0 3

Seq Scan on t_e20so1_document_datastore_default c_4 (cost=0.00..10.30 rows=30 width=226) (actual time=0.004..0.004 rows=0 loops=3)

17. 6.654 9.908 ↑ 1.0 10,025 3

Hash (cost=345.25..345.25 rows=10,025 width=11) (actual time=9.908..9.908 rows=10,025 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 554kB
18. 3.254 3.254 ↑ 1.0 10,025 3

Seq Scan on t_e20so1_pages b (cost=0.00..345.25 rows=10,025 width=11) (actual time=0.017..3.254 rows=10,025 loops=3)

Planning time : 2.263 ms