explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 14Jc

Settings
# exclusive inclusive rows x rows loops node
1. 59.993 1,770.787 ↓ 30,899.1 278,092 1

Unique (cost=31,749.88..32,186.71 rows=9 width=8) (actual time=124.357..1,770.787 rows=278,092 loops=1)

2. 14.539 1,710.794 ↓ 30,899.1 278,092 1

Nested Loop Left Join (cost=31,749.88..32,186.68 rows=9 width=8) (actual time=124.355..1,710.794 rows=278,092 loops=1)

3. 113.499 583.887 ↓ 30,899.1 278,092 1

Merge Left Join (cost=31,749.32..32,171.72 rows=9 width=8) (actual time=124.317..583.887 rows=278,092 loops=1)

  • Merge Cond: (table_0_1.c_documentid = table_1.c_documentid)
  • Filter: (NOT (SubPlan 3))
  • Rows Removed by Filter: 10
4. 242.926 470.133 ↓ 16,358.9 278,102 1

Merge Left Join (cost=31,749.05..32,168.64 rows=17 width=8) (actual time=124.299..470.133 rows=278,102 loops=1)

  • Merge Cond: (table_0_1.c_documentid = table_foldername6.c_documentid)
  • Filter: (((lower((table_0.c_custodianname)::text) = 'issystemtest2'::text) AND (NOT (SubPlan 1))) OR ((lower((table_0.c_custodianname)::text) = 'no custodian'::text) AND (NOT (SubPlan 2))))
  • Rows Removed by Filter: 2
5. 87.191 227.136 ↓ 80.7 278,104 1

Gather Merge (cost=31,019.34..31,430.16 rows=3,448 width=18) (actual time=124.216..227.136 rows=278,104 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 14.248 139.945 ↓ 62.5 69,526 4 / 4

Merge Left Join (cost=30,019.30..30,024.98 rows=1,112 width=18) (actual time=111.727..139.945 rows=69,526 loops=4)

  • Merge Cond: (table_0_1.c_documentid = table_foldername4.c_documentid)
7. 45.848 125.571 ↓ 62.5 69,526 4 / 4

Sort (cost=29,289.60..29,292.38 rows=1,112 width=17) (actual time=111.604..125.571 rows=69,526 loops=4)

  • Sort Key: table_0_1.c_documentid
  • Sort Method: external merge Disk: 2,328kB
  • Worker 0: Sort Method: external merge Disk: 2,216kB
  • Worker 1: Sort Method: external merge Disk: 2,048kB
  • Worker 2: Sort Method: external merge Disk: 2,152kB
8. 16.019 79.723 ↓ 62.5 69,526 4 / 4

Hash Join (cost=11.32..29,233.34 rows=1,112 width=17) (actual time=0.471..79.723 rows=69,526 loops=4)

  • Hash Cond: (table_0_1.c_custodianid = table_0.c_custodianid)
9. 6.101 63.345 ↑ 1.3 75,466 4 / 4

Parallel Append (cost=0.00..28,960.92 rows=97,892 width=16) (actual time=0.025..63.345 rows=75,466 loops=4)

10. 57.244 57.244 ↑ 1.3 75,466 4 / 4

Parallel Seq Scan on t_e20so1_document_datastore_11 table_0_1 (cost=0.00..28,471.46 rows=97,892 width=16) (actual time=0.024..57.244 rows=75,466 loops=4)

  • Filter: ((c_docoriginalid IS NULL) AND (c_docdisplayid IS NOT NULL) AND ((c_extension IS NOT NULL) OR (c_docdate IS NOT NULL)) AND (c_datastoresid = 11))
  • Rows Removed by Filter: 42,230
11. 0.006 0.359 ↑ 1.5 2 4 / 4

Hash (cost=11.28..11.28 rows=3 width=17) (actual time=0.358..0.359 rows=2 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.353 0.353 ↑ 1.5 2 4 / 4

Seq Scan on t_e20so1_custodian table_0 (cost=0.00..11.28 rows=3 width=17) (actual time=0.222..0.353 rows=2 loops=4)

  • Filter: ((lower((c_custodianname)::text) = 'issystemtest2'::text) OR (lower((c_custodianname)::text) = 'no custodian'::text))
  • Rows Removed by Filter: 274
13. 0.021 0.126 ↓ 1.5 38 4 / 4

Sort (cost=729.71..729.77 rows=25 width=9) (actual time=0.118..0.126 rows=38 loops=4)

  • Sort Key: table_foldername4.c_documentid
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Worker 2: Sort Method: quicksort Memory: 26kB
14. 0.004 0.105 ↓ 1.5 38 4 / 4

Append (cost=0.43..729.13 rows=25 width=9) (actual time=0.092..0.105 rows=38 loops=4)

15. 0.037 0.037 ↓ 0.0 0 4 / 4

Index Scan using t_e20so1_document_boolean_c_fieldid_idx on t_e20so1_document_boolean table_foldername4 (cost=0.43..700.62 rows=1 width=9) (actual time=0.037..0.037 rows=0 loops=4)

  • Index Cond: (c_fieldid = 'fd6fb4d6-49e8-48d2-b5e5-9a8b7a2f96b8'::uuid)
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
16. 0.064 0.064 ↓ 1.6 38 4 / 4

Index Scan using uc_unique_t_e20so1_document_boolean_folders on t_e20so1_document_boolean_folders table_foldername4_1 (cost=0.42..28.38 rows=24 width=9) (actual time=0.054..0.064 rows=38 loops=4)

  • Index Cond: (c_fieldid = 'fd6fb4d6-49e8-48d2-b5e5-9a8b7a2f96b8'::uuid)
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
17. 0.015 0.071 ↓ 1.4 34 1

Sort (cost=729.71..729.77 rows=25 width=9) (actual time=0.065..0.071 rows=34 loops=1)

  • Sort Key: table_foldername6.c_documentid
  • Sort Method: quicksort Memory: 26kB
18. 0.004 0.056 ↓ 1.4 34 1

Append (cost=0.43..729.13 rows=25 width=9) (actual time=0.038..0.056 rows=34 loops=1)

19. 0.019 0.019 ↓ 0.0 0 1

Index Scan using t_e20so1_document_boolean_c_fieldid_idx on t_e20so1_document_boolean table_foldername6 (cost=0.43..700.62 rows=1 width=9) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (c_fieldid = '8c3fcf58-17df-4e80-8f8f-bc0fdc238872'::uuid)
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
20. 0.033 0.033 ↓ 1.4 34 1

Index Scan using uc_unique_t_e20so1_document_boolean_folders on t_e20so1_document_boolean_folders table_foldername6_1 (cost=0.42..28.38 rows=24 width=9) (actual time=0.017..0.033 rows=34 loops=1)

  • Index Cond: (c_fieldid = '8c3fcf58-17df-4e80-8f8f-bc0fdc238872'::uuid)
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
21.          

SubPlan (for Merge Left Join)

22. 0.000 0.000 ↓ 0.0 0 270,084

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=270,084)

  • One-Time Filter: table_foldername4.c_value
23. 0.000 0.000 ↓ 0.0 0 8,020

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=8,020)

  • One-Time Filter: table_foldername6.c_value
24. 0.255 0.255 ↑ 2.6 178 1

Index Scan using "UQ_328227fb-363e-467d-90b2-4411a5dcb524" on t_e20so1_document_tag8 table_1 (cost=0.27..18.74 rows=456 width=10) (actual time=0.014..0.255 rows=178 loops=1)

25.          

SubPlan (for Merge Left Join)

26. 0.000 0.000 ↓ 0.0 0 278,102

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=278,102)

  • One-Time Filter: (table_1.c_krishna_11_f56 AND table_1.c_krishna_12_q30)
27. 278.092 1,112.368 ↓ 0.0 0 278,092

Append (cost=0.56..1.64 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=278,092)

28. 556.184 556.184 ↓ 0.0 0 278,092

Index Scan using "UQ_83a9a5dc-5667-4aeb-92f4-efa3d9184431" on t_e20so1_document_boolean table_2 (cost=0.56..1.16 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=278,092)

  • Index Cond: ((c_fieldid = '136f9e73-7000-4f47-a1b7-1dbb0e9d0035'::uuid) AND (table_0_1.c_documentid = c_documentid))
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)
29. 278.092 278.092 ↓ 0.0 0 278,092

Index Scan using uc_unique_t_e20so1_document_boolean_folders on t_e20so1_document_boolean_folders table_2_1 (cost=0.42..0.48 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=278,092)

  • Index Cond: ((c_fieldid = '136f9e73-7000-4f47-a1b7-1dbb0e9d0035'::uuid) AND (table_0_1.c_documentid = c_documentid))
  • Filter: (c_tablegroupsid = 'e4308ac6-c388-11e7-8c54-9b97e6527505'::uuid)