explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jm6a : Optimization for: Test; plan #XPFH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 8,345.746 ↓ 0.0 0 1

WindowAgg (cost=45,073,669.82..45,073,669.84 rows=1 width=2,856) (actual time=8,345.746..8,345.746 rows=0 loops=1)

2. 0.000 8,345.744 ↓ 0.0 0 1

Sort (cost=45,073,669.82..45,073,669.82 rows=1 width=2,848) (actual time=8,345.744..8,345.744 rows=0 loops=1)

  • Sort Key: a.rno
  • Sort Method: quicksort Memory: 25kB
3. 364.914 8,376.258 ↓ 0.0 0 1

Gather (cost=1,178,051.40..45,073,669.81 rows=1 width=2,848) (actual time=8,345.739..8,376.258 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 218.316 8,011.344 ↓ 0.0 0 3

Hash Join (cost=1,177,051.40..45,072,669.71 rows=1 width=2,848) (actual time=8,011.344..8,011.344 rows=0 loops=3)

  • Hash Cond: (lower((a.newbegbates)::text) = lower((b.c_pagealias)::text))
5. 290.293 7,784.125 ↓ 333,333.0 333,333 3

Merge Left Join (cost=1,176,580.84..45,072,194.89 rows=1 width=2,848) (actual time=6,876.355..7,784.125 rows=333,333 loops=3)

  • Merge Cond: ((lower((a.newbegbates)::text)) = (lower((c.c_docdisplayid)::text)))
  • Filter: (c.c_documentid IS NULL)
6. 4,570.160 5,469.111 ↑ 1.3 333,333 3

Sort (cost=1,026,102.36..1,027,152.15 rows=419,915 width=2,848) (actual time=4,851.607..5,469.111 rows=333,333 loops=3)

  • Sort Key: (lower((a.newbegbates)::text))
  • Sort Method: external merge Disk: 471728kB
  • Worker 0: Sort Method: external merge Disk: 475648kB
  • Worker 1: Sort Method: external merge Disk: 476512kB
7. 898.951 898.951 ↑ 1.3 333,333 3

Parallel Seq Scan on temp_dat_10032019095701_72056 a (cost=0.00..383,293.15 rows=419,915 width=2,848) (actual time=0.043..898.951 rows=333,333 loops=3)

8. 0.018 2,024.721 ↑ 17,134.1 61 3

Materialize (cost=150,478.48..155,704.39 rows=1,045,183 width=20) (actual time=2,024.682..2,024.721 rows=61 loops=3)

9. 1,280.659 2,024.703 ↑ 17,134.1 61 3

Sort (cost=150,478.48..153,091.44 rows=1,045,183 width=20) (actual time=2,024.678..2,024.703 rows=61 loops=3)

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

Result (cost=0.00..33,428.37 rows=1,045,183 width=20) (actual time=0.067..744.044 rows=1,044,906 loops=3)

11. 88.213 268.130 ↑ 1.0 1,044,906 3

Append (cost=0.00..33,428.37 rows=1,045,183 width=20) (actual time=0.059..268.130 rows=1,044,906 loops=3)

12. 0.018 0.018 ↓ 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.018..0.018 rows=0 loops=3)

13. 0.195 0.195 ↑ 1.0 997 3

Index Only Scan using t_e20so1_document_datastore_11_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_11 c_1 (cost=0.28..26.23 rows=997 width=31) (actual time=0.040..0.195 rows=997 loops=3)

  • Heap Fetches: 0
14. 179.669 179.669 ↑ 1.0 1,043,880 3

Index Only Scan using t_e20so1_document_datastore_12_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_12 c_2 (cost=0.42..28,153.03 rows=1,044,067 width=20) (actual time=0.028..179.669 rows=1,043,880 loops=3)

  • Heap Fetches: 0
15. 0.031 0.031 ↑ 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.022..0.031 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. 5.931 8.903 ↑ 1.0 10,025 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 554kB
18. 2.972 2.972 ↑ 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.021..2.972 rows=10,025 loops=3)

Planning time : 2.389 ms