explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9tDQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 1,524.392 ↑ 391.6 28 1

Sort (cost=107,122.37..107,149.78 rows=10,965 width=808) (actual time=1,524.390..1,524.392 rows=28 loops=1)

  • Output: f.base_id, f.base_qcode, f.base_type, f.base_etag, f.base_original, f.base_cr_time, f.base_status, f.md_id, f.ext_value, f.ext_rel, f.ext_status, f.up_time, (CASE
  • Sort Key: f.base_id, f.md_id, f.ext_rel, (CASE WHEN ((f.ext_status)::text = 'V'::text) THEN 1 WHEN ((f.ext_status)::text = 'M'::text) THEN 2 WHEN ((f.ext_status)::text = '
  • Sort Method: quicksort Memory: 31kB
2. 0.013 1,524.263 ↑ 391.6 28 1

Subquery Scan on f (cost=101,911.92..102,487.59 rows=10,965 width=808) (actual time=1,524.235..1,524.263 rows=28 loops=1)

  • Output: f.base_id, f.base_qcode, f.base_type, f.base_etag, f.base_original, f.base_cr_time, f.base_status, f.md_id, f.ext_value, f.ext_rel, f.ext_status, f.up_time,
3. 0.017 1,524.250 ↑ 391.6 28 1

Unique (cost=101,911.92..102,268.29 rows=10,965 width=804) (actual time=1,524.233..1,524.250 rows=28 loops=1)

  • Output: t.pers_id, t.pers_code, t.pers_type, te.etag, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ext.up_time
4. 0.222 1,524.233 ↑ 391.6 28 1

Sort (cost=101,911.92..101,939.34 rows=10,965 width=804) (actual time=1,524.231..1,524.233 rows=28 loops=1)

  • Output: t.pers_id, t.pers_code, t.pers_type, te.etag, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ext.up_time
  • Sort Key: t.pers_id, t.pers_code, t.pers_type, te.etag, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ext.up_time
  • Sort Method: quicksort Memory: 31kB
5. 0.006 1,524.011 ↑ 391.6 28 1

Append (cost=56,974.82..97,277.14 rows=10,965 width=804) (actual time=830.581..1,524.011 rows=28 loops=1)

6. 43.039 929.439 ↑ 411.7 22 1

Hash Join (cost=56,974.82..66,241.79 rows=9,057 width=99) (actual time=830.580..929.439 rows=22 loops=1)

  • Output: t.pers_id, t.pers_code, t.pers_type, te.etag, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ext.u
  • Hash Cond: (te.pers_id = t.pers_id)
7. 56.842 56.842 ↑ 1.0 335,470 1

Seq Scan on ref.tb_pers_etag te (cost=0.00..5,821.70 rows=335,470 width=16) (actual time=0.010..56.842 rows=335,470 loops=1)

  • Output: te.pers_id, te.etag, te.vercnt, te.usecnt
8. 0.000 829.558 ↑ 411.7 22 1

Hash (cost=56,861.61..56,861.61 rows=9,057 width=99) (actual time=829.558..829.558 rows=22 loops=1)

  • Output: t.pers_id, t.pers_code, t.pers_type, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ext.up_t
  • Buckets: 16384 Batches: 1 Memory Usage: 132kB
9. 18.692 829.652 ↑ 411.7 22 1

Gather (cost=13,662.80..56,861.61 rows=9,057 width=99) (actual time=234.648..829.652 rows=22 loops=1)

  • Output: t.pers_id, t.pers_code, t.pers_type, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.status, ex
  • Workers Planned: 2
  • Workers Launched: 2
10. 517.587 810.960 ↑ 539.1 7 3

Hash Join (cost=12,662.80..54,955.91 rows=3,774 width=99) (actual time=614.127..810.960 rows=7 loops=3)

  • Output: t.pers_id, t.pers_code, t.pers_type, t.is_dup_of, t.cr_time, t.status, ext.md_id, ext.lb_value, ext.lg_id, ext.stat
  • Hash Cond: (ext.pers_id = t.pers_id)
  • Worker 0: actual time=798.652..798.652 rows=0 loops=1
  • Worker 1: actual time=809.523..809.523 rows=0 loops=1
  • -> Parallel Seq Scan on ref.tb_pers_lb ext (cost=0.00..39290.30 rows=790687 width=46) (actual time=0.018..409.954 rows=63
  • Output: ext.pers_id, ext.lg_id, ext.md_id, ext.lb_value, ext.cr_time, ext.cr_by, ext.up_time, ext.up_by, ext.status
  • Filter: ((ext.status)::text = ANY ('{C,D,M,V}'::text[]))
  • Rows Removed by Filter: 11140
  • Worker 0: actual time=0.021..358.796 rows=569680 loops=1
  • Worker 1: actual time=0.021..409.767 rows=623175 loops=1
11. 0.005 293.373 ↑ 1,601.0 1 3

Hash (cost=12,642.78..12,642.78 rows=1,601 width=53) (actual time=293.373..293.373 rows=1 loops=3)

  • Output: t.pers_id, t.pers_code, t.pers_type, t.is_dup_of, t.cr_time, t.status
  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
  • Worker 0: actual time=326.817..326.817 rows=1 loops=1
  • Worker 1: actual time=319.808..319.808 rows=1 loops=1
12. 293.368 293.368 ↑ 1,601.0 1 3

Seq Scan on ref.tb_pers t (cost=0.00..12,642.78 rows=1,601 width=53) (actual time=0.405..293.368 rows=1 loops=3)

  • Output: t.pers_id, t.pers_code, t.pers_type, t.is_dup_of, t.cr_time, t.status
  • Filter: (((t.status)::text = ANY ('{C,D,M,V}'::text[])) AND (((t.pers_id)::character varying)::text = '5060'::t
  • Rows Removed by Filter: 335456
  • Worker 0: actual time=0.387..326.811 rows=1 loops=1
  • Worker 1: actual time=0.465..319.802 rows=1 loops=1
13. 80.841 594.566 ↑ 318.0 6 1

Hash Join (cost=16,535.76..30,925.69 rows=1,908 width=124) (actual time=264.338..594.566 rows=6 loops=1)

  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, te_1.etag, t_1.is_dup_of, t_1.cr_time, t_1.status, ext_1.md_id, ext_1.ext_value, ext_1.ext
  • Hash Cond: (ext_1.pers_id = t_1.pers_id)
14. 251.789 251.789 ↑ 1.0 399,691 1

Seq Scan on ref.tb_pers_ext ext_1 (cost=0.00..12,871.66 rows=399,786 width=71) (actual time=0.014..251.789 rows=399,691 loops=1)

  • Output: ext_1.pers_id, ext_1.md_id, ext_1.ext_value, ext_1.ext_rel, ext_1.cr_time, ext_1.cr_by, ext_1.up_time, ext_1.up_by, ext_1.statu
  • Filter: ((ext_1.status)::text = ANY ('{C,D,M,V}'::text[]))
  • Rows Removed by Filter: 3753
15. 0.015 261.936 ↑ 1,601.0 1 1

Hash (cost=16,515.74..16,515.74 rows=1,601 width=69) (actual time=261.936..261.936 rows=1 loops=1)

  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, t_1.is_dup_of, t_1.cr_time, t_1.status, te_1.etag, te_1.pers_id
  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
16. 59.474 261.921 ↑ 1,601.0 1 1

Hash Join (cost=9,420.02..16,515.74 rows=1,601 width=69) (actual time=143.379..261.921 rows=1 loops=1)

  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, t_1.is_dup_of, t_1.cr_time, t_1.status, te_1.etag, te_1.pers_id
  • Hash Cond: (te_1.pers_id = t_1.pers_id)
17. 59.810 59.810 ↑ 1.0 335,470 1

Seq Scan on ref.tb_pers_etag te_1 (cost=0.00..5,821.70 rows=335,470 width=16) (actual time=0.007..59.810 rows=335,470 loops=1)

  • Output: te_1.pers_id, te_1.etag, te_1.vercnt, te_1.usecnt
18. 0.000 142.637 ↑ 1,601.0 1 1

Hash (cost=9,400.01..9,400.01 rows=1,601 width=53) (actual time=142.637..142.637 rows=1 loops=1)

  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, t_1.is_dup_of, t_1.cr_time, t_1.status
  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
19. 142.670 142.670 ↑ 1,601.0 1 1

Gather (cost=1,000.00..9,400.01 rows=1,601 width=53) (actual time=0.590..142.670 rows=1 loops=1)

  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, t_1.is_dup_of, t_1.cr_time, t_1.status
  • Workers Planned: 2
  • Workers Launched: 2
  • -> Parallel Seq Scan on ref.tb_pers t_1 (cost=0.00..8239.91 rows=667 width=53) (actual time=70.373..117.716 rows=0
  • Output: t_1.pers_id, t_1.pers_code, t_1.pers_type, t_1.is_dup_of, t_1.cr_time, t_1.status
  • Filter: (((t_1.status)::text = ANY ('{C,D,M,V}'::text[])) AND (((t_1.pers_id)::character varying)::text = '5060
  • Rows Removed by Filter: 111819
  • Worker 0: actual time=103.367..103.367 rows=0 loops=1
  • Worker 1: actual time=107.431..107.431 rows=0 loops=1
Planning time : 1.568 ms
Execution time : 1,524.783 ms