explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4aPY : Optimization for: plan #OR5G

Settings

Optimization path:

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

WindowAgg (cost=74,333,005.54..78,867,727.95 rows=259,126,995 width=159) (actual time=2,692.337..2,692.337 rows=0 loops=1)

2. 0.004 2,692.335 ↓ 0.0 0 1

Sort (cost=74,333,005.54..74,980,823.03 rows=259,126,995 width=143) (actual time=2,692.335..2,692.335 rows=0 loops=1)

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

Merge Join (cost=253,658.78..5,439,307.49 rows=259,126,995 width=143) (actual time=2,692.331..2,692.331 rows=0 loops=1)

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

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

  • Sort Key: (lower((temp_opt_10032019095701_72056.pagelabel)::text))
  • Sort Method: external merge Disk: 5568kB
5. 68.430 68.430 ↑ 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.094..68.430 rows=40,000 loops=1)

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

Materialize (cost=203,224.82..208,450.73 rows=1,045,183 width=12) (actual time=2,535.336..2,535.376 rows=61 loops=1)

7. 1,264.527 2,535.365 ↑ 17,134.1 61 1

Sort (cost=203,224.82..205,837.78 rows=1,045,183 width=12) (actual time=2,535.331..2,535.365 rows=61 loops=1)

  • Sort Key: (lower((b.c_docdisplayid)::text))
  • Sort Method: external merge Disk: 35712kB
8. 581.007 1,270.838 ↑ 1.0 1,044,906 1

Hash Join (cost=34,953.52..88,267.75 rows=1,045,183 width=12) (actual time=362.164..1,270.838 rows=1,044,906 loops=1)

  • Hash Cond: (b.c_datastoresid = t_e20so1_document_datastore_1.c_datastoresid)
9. 82.318 327.702 ↑ 1.0 1,044,906 1

Append (cost=0.00..46,694.75 rows=1,045,183 width=20) (actual time=0.015..327.702 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=12) (actual time=0.005..0.005 rows=0 loops=1)

11. 0.270 0.270 ↑ 1.0 997 1

Seq Scan on t_e20so1_document_datastore_11 b_1 (cost=0.00..37.97 rows=997 width=31) (actual time=0.009..0.270 rows=997 loops=1)

12. 245.090 245.090 ↑ 1.0 1,043,880 1

Seq Scan on t_e20so1_document_datastore_12 b_2 (cost=0.00..41,407.67 rows=1,044,067 width=20) (actual time=0.005..245.090 rows=1,043,880 loops=1)

13. 0.016 0.016 ↑ 1.0 29 1

Seq Scan on t_e20so1_document_datastore_13 b_3 (cost=0.00..2.29 rows=29 width=20) (actual time=0.009..0.016 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=226) (actual time=0.003..0.003 rows=0 loops=1)

15. 0.005 362.129 ↑ 1.0 3 1

Hash (cost=34,953.48..34,953.48 rows=3 width=8) (actual time=362.129..362.129 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 154.833 362.124 ↑ 1.0 3 1

HashAggregate (cost=34,953.42..34,953.45 rows=3 width=8) (actual time=362.123..362.124 rows=3 loops=1)

  • Group Key: t_e20so1_document_datastore_1.c_datastoresid
17. 80.821 207.291 ↑ 1.0 1,044,906 1

Append (cost=0.00..32,340.47 rows=1,045,183 width=8) (actual time=0.023..207.291 rows=1,044,906 loops=1)

18. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on t_e20so1_document_datastore_1 (cost=0.00..10.60 rows=60 width=8) (actual time=0.002..0.002 rows=0 loops=1)

19. 0.130 0.130 ↑ 1.0 997 1

Index Only Scan using t_e20so1_document_datastore_11_c_datastoresid_idx on t_e20so1_document_datastore_11 (cost=0.28..21.83 rows=997 width=8) (actual time=0.021..0.130 rows=997 loops=1)

  • Heap Fetches: 0
20. 126.313 126.313 ↑ 1.0 1,043,880 1

Index Only Scan using t_e20so1_document_datastore_12_c_datastoresid_idx on t_e20so1_document_datastore_12 (cost=0.42..27,069.53 rows=1,044,067 width=8) (actual time=0.016..126.313 rows=1,043,880 loops=1)

  • Heap Fetches: 0
21. 0.022 0.022 ↑ 1.0 29 1

Seq Scan on t_e20so1_document_datastore_13 (cost=0.00..2.29 rows=29 width=8) (actual time=0.013..0.022 rows=29 loops=1)

22. 0.003 0.003 ↓ 0.0 0 1

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

Planning time : 0.801 ms