explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ZQ3 : Optimization for: plan #VGCn

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,002.889 4,986.510 ↓ 90.7 1,120,934 1

Gather (cost=208,421.75..233,346.39 rows=12,361 width=12) (actual time=1,519.909..4,986.510 rows=1,120,934 loops=1)

  • Workers Planned: 3
  • Params Evaluated: $0
  • Workers Launched: 3
2.          

Initplan (for Gather)

3. 0.005 0.028 ↑ 1.0 1 1

Aggregate (cost=11.00..11.01 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)

4. 0.023 0.023 ↑ 40.0 2 1

Seq Scan on temp_refinesearch_ktno5tln3lmigqhdym1bdgps refine_search (cost=0.00..10.80 rows=80 width=4) (actual time=0.023..0.023 rows=2 loops=1)

5. 79.957 3,983.593 ↓ 70.3 280,234 4 / 4

Hash Join (cost=207,410.74..231,099.28 rows=3,987 width=12) (actual time=1,574.061..3,983.593 rows=280,234 loops=4)

  • Hash Cond: (table_1.c_doctype = table_0.c_enumid)
6. 81.827 3,903.604 ↓ 70.3 280,234 4 / 4

Nested Loop (cost=207,409.63..231,043.35 rows=3,987 width=12) (actual time=1,573.986..3,903.604 rows=280,234 loops=4)

7. 0.000 2,981.076 ↓ 70.3 280,234 4 / 4

Nested Loop (cost=207,409.20..228,940.13 rows=3,987 width=24) (actual time=1,573.937..2,981.076 rows=280,234 loops=4)

8. 175.740 2,152.257 ↓ 70.3 280,234 4 / 4

Merge Join (cost=207,408.77..227,079.24 rows=3,987 width=16) (actual time=1,573.794..2,152.257 rows=280,234 loops=4)

  • Merge Cond: (temp_drilldown_ktno5tln3lmigqhdym1bdgps."_PKID" = doc.c_documentid)
9. 195.278 278.047 ↓ 70.3 280,234 4 / 4

Sort (cost=22,326.30..22,336.27 rows=3,987 width=8) (actual time=226.302..278.047 rows=280,234 loops=4)

  • Sort Key: temp_drilldown_ktno5tln3lmigqhdym1bdgps."_PKID
  • Sort Method: external merge Disk: 6760kB
  • Worker 0: Sort Method: external merge Disk: 6472kB
  • Worker 1: Sort Method: external merge Disk: 3280kB
  • Worker 2: Sort Method: external merge Disk: 3328kB
10. 82.769 82.769 ↓ 70.3 280,234 4 / 4

Parallel Seq Scan on temp_drilldown_ktno5tln3lmigqhdym1bdgps (cost=0.00..22,087.85 rows=3,987 width=8) (actual time=33.456..82.769 rows=280,234 loops=4)

  • Filter: (c_level = $0)
  • Rows Removed by Filter: 280234
11. 185.875 1,698.470 ↓ 1.0 1,120,798 4 / 4

Unique (cost=185,082.48..190,685.52 rows=1,120,609 width=8) (actual time=1,347.466..1,698.470 rows=1,120,798 loops=4)

12. 830.952 1,512.595 ↓ 1.0 1,120,798 4 / 4

Sort (cost=185,082.48..187,884.00 rows=1,120,609 width=8) (actual time=1,347.458..1,512.595 rows=1,120,798 loops=4)

  • Sort Key: doc.c_documentid
  • Sort Method: external merge Disk: 19752kB
  • Worker 0: Sort Method: external merge Disk: 19752kB
  • Worker 1: Sort Method: external merge Disk: 19752kB
  • Worker 2: Sort Method: external merge Disk: 19752kB
13. 211.598 681.643 ↓ 1.0 1,120,936 4 / 4

Hash Join (cost=2.12..54,534.71 rows=1,120,609 width=8) (actual time=0.125..681.643 rows=1,120,936 loops=4)

  • Hash Cond: (doc.c_datasetid = dataset.c_datasetsid)
14. 90.805 470.014 ↓ 1.0 1,120,936 4 / 4

Append (cost=0.00..51,341.66 rows=1,120,609 width=16) (actual time=0.038..470.014 rows=1,120,936 loops=4)

15. 379.209 379.209 ↓ 1.0 1,120,936 4 / 4

Seq Scan on t_e20so1_document_datastore_16 doc (cost=0.00..45,738.61 rows=1,120,609 width=16) (actual time=0.037..379.209 rows=1,120,936 loops=4)

  • Filter: (c_datastoresid = 16)
16. 0.010 0.031 ↑ 1.0 50 4 / 4

Hash (cost=1.50..1.50 rows=50 width=8) (actual time=0.031..0.031 rows=50 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.021 0.021 ↑ 1.0 50 4 / 4

Seq Scan on t_e20so1_datasets dataset (cost=0.00..1.50 rows=50 width=8) (actual time=0.014..0.021 rows=50 loops=4)

18. 280.234 840.701 ↑ 1.0 1 1,120,934 / 4

Append (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,120,934)

19. 560.467 560.467 ↑ 1.0 1 1,120,934 / 4

Index Only Scan using t_e20so1_document_datastore_16_pkey on t_e20so1_document_datastore_16 document (cost=0.43..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,120,934)

  • Index Cond: ((c_documentid = doc.c_documentid) AND (c_datastoresid = 16))
  • Heap Fetches: 0
20. 840.701 840.701 ↑ 1.0 1 1,120,934 / 4

Index Scan using t_e20so1_document_property_c_documentid_idx on t_e20so1_document_property table_1 (cost=0.43..0.53 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,120,934)

  • Index Cond: (c_documentid = doc.c_documentid)
21. 0.004 0.032 ↑ 1.0 5 4 / 4

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.032..0.032 rows=5 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.028 0.028 ↑ 1.0 5 4 / 4

Seq Scan on t_e20so1_filetype table_0 (cost=0.00..1.05 rows=5 width=4) (actual time=0.027..0.028 rows=5 loops=4)

Planning time : 11.711 ms