explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OR5G

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 2,090.936 ↓ 0.0 0 1

WindowAgg (cost=74,275,415.05..78,810,137.46 rows=259,126,995 width=159) (actual time=2,090.936..2,090.936 rows=0 loops=1)

2. 0.004 2,090.934 ↓ 0.0 0 1

Sort (cost=74,275,415.05..74,923,232.54 rows=259,126,995 width=143) (actual time=2,090.934..2,090.934 rows=0 loops=1)

  • Sort Key: temp_opt_10032019095701_72056.rno
  • Sort Method: quicksort Memory: 25kB
3. 8.571 2,090.930 ↓ 0.0 0 1

Merge Join (cost=196,068.29..5,381,717.00 rows=259,126,995 width=143) (actual time=2,090.930..2,090.930 rows=0 loops=1)

  • Merge Cond: ((lower((temp_opt_10032019095701_72056.pagelabel)::text)) = (lower((b.c_docdisplayid)::text)))
4. 78.496 145.123 ↑ 1.2 40,000 1

Sort (cost=50,433.96..50,557.93 rows=49,585 width=143) (actual time=130.692..145.123 rows=40,000 loops=1)

  • Sort Key: (lower((temp_opt_10032019095701_72056.pagelabel)::text))
  • Sort Method: external merge Disk: 5568kB
5. 66.627 66.627 ↑ 1.2 40,000 1

Seq Scan on temp_opt_10032019095701_72056 (cost=0.00..46,566.93 rows=49,585 width=143) (actual time=0.093..66.627 rows=40,000 loops=1)

  • Filter: (COALESCE((docbreak)::text, ''::text) <> 'Y'::text)
  • Rows Removed by Filter: 9999
6. 0.012 1,937.236 ↑ 17,134.1 61 1

Materialize (cost=145,634.33..150,860.24 rows=1,045,183 width=12) (actual time=1,937.199..1,937.236 rows=61 loops=1)

7. 1,233.563 1,937.224 ↑ 17,134.1 61 1

Sort (cost=145,634.33..148,247.29 rows=1,045,183 width=12) (actual time=1,937.194..1,937.224 rows=61 loops=1)

  • Sort Key: (lower((b.c_docdisplayid)::text))
  • Sort Method: external merge Disk: 35736kB
8. 474.105 703.661 ↑ 1.0 1,044,906 1

Result (cost=0.00..30,677.27 rows=1,045,183 width=12) (actual time=0.034..703.661 rows=1,044,906 loops=1)

9. 83.743 229.556 ↑ 1.0 1,044,906 1

Append (cost=0.00..30,677.27 rows=1,045,183 width=12) (actual time=0.024..229.556 rows=1,044,906 loops=1)

10. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on t_e20so1_document_datastore_1 b (cost=0.00..10.60 rows=60 width=4) (actual time=0.005..0.005 rows=0 loops=1)

11. 0.143 0.143 ↑ 1.0 997 1

Index Only Scan using t_e20so1_document_datastore_11_c_docdisplayid_idx on t_e20so1_document_datastore_11 b_1 (cost=0.28..25.13 rows=997 width=23) (actual time=0.018..0.143 rows=997 loops=1)

  • Heap Fetches: 0
12. 145.642 145.642 ↑ 1.0 1,043,880 1

Index Only Scan using t_e20so1_document_datastore_12_c_docdisplayid_idx on t_e20so1_document_datastore_12 b_2 (cost=0.42..25,403.03 rows=1,044,067 width=12) (actual time=0.011..145.642 rows=1,043,880 loops=1)

  • Heap Fetches: 0
13. 0.020 0.020 ↑ 1.0 29 1

Seq Scan on t_e20so1_document_datastore_13 b_3 (cost=0.00..2.29 rows=29 width=12) (actual time=0.012..0.020 rows=29 loops=1)

14. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t_e20so1_document_datastore_default b_4 (cost=0.00..10.30 rows=30 width=218) (actual time=0.003..0.003 rows=0 loops=1)

Planning time : 0.484 ms