explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lqex

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7,290.547 ↓ 0.0 0 1

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

2. 0.004 7,290.545 ↓ 0.0 0 1

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

  • Sort Key: a.rno
  • Sort Method: quicksort Memory: 25kB
3. 7.871 7,290.541 ↓ 0.0 0 1

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

  • Merge Cond: ((lower((a.pagelabel)::text)) = (lower((b.c_docdisplayid)::text)))
4. 91.727 156.738 ↑ 1.2 40,000 1

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

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

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

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

Materialize (cost=203,224.82..208,450.73 rows=1,045,183 width=12) (actual time=7,125.892..7,125.932 rows=61 loops=1)

7. 1,264.664 7,125.919 ↑ 17,134.1 61 1

Sort (cost=203,224.82..205,837.78 rows=1,045,183 width=12) (actual time=7,125.886..7,125.919 rows=61 loops=1)

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

Hash Join (cost=34,953.52..88,267.75 rows=1,045,183 width=12) (actual time=4,945.114..5,861.255 rows=1,044,906 loops=1)

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

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

10. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

11. 0.268 0.268 ↑ 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.008..0.268 rows=997 loops=1)

12. 251.593 251.593 ↑ 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.006..251.593 rows=1,043,880 loops=1)

13. 0.021 0.021 ↑ 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.010..0.021 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.008 4,945.079 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 170.945 4,945.071 ↑ 1.0 3 1

HashAggregate (cost=34,953.42..34,953.45 rows=3 width=8) (actual time=4,945.070..4,945.071 rows=3 loops=1)

  • Group Key: t_e20so1_document_datastore_1.c_datastoresid
17. 86.080 4,774.126 ↑ 1.0 1,044,906 1

Append (cost=0.00..32,340.47 rows=1,045,183 width=8) (actual time=2.492..4,774.126 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. 5.938 5.938 ↑ 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=2.489..5.938 rows=997 loops=1)

  • Heap Fetches: 0
20. 4,682.081 4,682.081 ↑ 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=5.085..4,682.081 rows=1,043,880 loops=1)

  • Heap Fetches: 0
21. 0.021 0.021 ↑ 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.021 rows=29 loops=1)

22. 0.004 0.004 ↓ 0.0 0 1

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

Planning time : 0.859 ms