explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IWIP

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 8.748 ↓ 2.0 2 1

Sort (cost=135.83..135.84 rows=1 width=573) (actual time=8.747..8.748 rows=2 loops=1)

  • Output: c.id, c.fio, (concat_ws(' '::text, split_part((spr1."values")::text, '|'::text, 1), split_part((spr2."values")::text, '|'::text, 1), split_part((spr3."values")::text, '|'::text, 1))), spr1."values", spr2."values", spr3."values", c.id, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress
  • Sort Key: (concat_ws(' '::text, split_part((spr1."values")::text, '|'::text, 1), split_part((spr2."values")::text, '|'::text, 1), split_part((spr3."values")::text, '|'::text, 1)))
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=173
2. 0.180 8.720 ↓ 2.0 2 1

Nested Loop (cost=32.78..135.82 rows=1 width=573) (actual time=2.047..8.720 rows=2 loops=1)

  • Output: c.id, c.fio, concat_ws(' '::text, split_part((spr1."values")::text, '|'::text, 1), split_part((spr2."values")::text, '|'::text, 1), split_part((spr3."values")::text, '|'::text, 1)), spr1."values", spr2."values", spr3."values", c.id, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress
  • Buffers: shared hit=173
3. 0.012 7.052 ↓ 6.0 6 1

Nested Loop (cost=29.58..104.72 rows=1 width=473) (actual time=1.555..7.052 rows=6 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, spr1."values", p2.par5, p2.new5, spr3."values
  • Buffers: shared hit=119
4. 0.164 7.002 ↓ 2.0 2 1

Nested Loop (cost=29.02..99.68 rows=1 width=473) (actual time=1.546..7.002 rows=2 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, p1.old5, spr1."values", p3.old5, spr3."values
  • Buffers: shared hit=105
5. 0.022 1.762 ↓ 6.0 6 1

Nested Loop (cost=25.82..68.59 rows=1 width=459) (actual time=1.390..1.762 rows=6 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, p1.old5, p1.par5, p1.new5, p3.old5, spr3."values
  • Buffers: shared hit=51
6. 1.091 1.682 ↓ 2.0 2 1

Hash Join (cost=25.26..63.55 rows=1 width=411) (actual time=1.376..1.682 rows=2 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, p3.old5, spr3."values
  • Hash Cond: (((md5((spr3.pattern_id)::text))::uuid = p3.par5) AND ((md5((spr3.id)::text))::uuid = p3.new5))
  • Buffers: shared hit=37
7. 0.441 0.441 ↑ 1.0 104 1

Seq Scan on public.inddev_spr spr3 (cost=0.00..36.20 rows=104 width=54) (actual time=0.346..0.441 rows=104 loops=1)

  • Output: spr3.id, spr3."values", spr3.pattern_id
  • Filter: (spr3.pattern_id = 5)
  • Rows Removed by Filter: 1432
  • Buffers: shared hit=17
8. 0.012 0.150 ↓ 2.0 6 1

Hash (cost=25.22..25.22 rows=3 width=397) (actual time=0.150..0.150 rows=6 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, p3.old5, p3.par5, p3.new5
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=20
9. 0.030 0.138 ↓ 2.0 6 1

Nested Loop (cost=1.13..25.22 rows=3 width=397) (actual time=0.111..0.138 rows=6 loops=1)

  • Output: c.id, c.fio, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress, p3.old5, p3.par5, p3.new5
  • Buffers: shared hit=20
10. 0.080 0.080 ↓ 2.0 2 1

Index Scan using inddev_client_fio_idx on public.inddev_client c (cost=0.56..8.57 rows=1 width=349) (actual time=0.077..0.080 rows=2 loops=1)

  • Output: c.id, c.f, c.i, c.o, c.sex, c.birth_date, c.dul, c.fio, c.fio_rp, c.fio_en, c.fi1o1, c.fi1o1_en, c.fi_en, c.adress
  • Index Cond: ((c.fio)::text = 'Ладомирский Валентин Назарович'::text)
  • Buffers: shared hit=6
11. 0.028 0.028 ↑ 1.0 3 2

Index Scan using inddev_para_old5_idx on public.inddev_para p3 (cost=0.57..16.61 rows=3 width=48) (actual time=0.010..0.014 rows=3 loops=2)

  • Output: p3.id, p3.old5, p3.new5, p3.par5
  • Index Cond: (p3.old5 = (md5((c.fio)::text))::uuid)
  • Buffers: shared hit=14
12. 0.058 0.058 ↑ 1.0 3 2

Index Scan using inddev_para_old5_idx on public.inddev_para p1 (cost=0.56..5.01 rows=3 width=48) (actual time=0.012..0.029 rows=3 loops=2)

  • Output: p1.id, p1.old5, p1.new5, p1.par5
  • Index Cond: (p1.old5 = p3.old5)
  • Buffers: shared hit=14
13. 4.782 5.076 ↓ 0.0 0 6

Bitmap Heap Scan on public.inddev_spr spr1 (cost=3.20..31.08 rows=1 width=54) (actual time=0.094..0.846 rows=0 loops=6)

  • Output: spr1.id, spr1."values", spr1.pattern_id
  • Recheck Cond: ((md5((spr1.pattern_id)::text))::uuid = p1.par5)
  • Filter: ((spr1.pattern_id = 1) AND (p1.new5 = (md5((spr1.id)::text))::uuid))
  • Rows Removed by Filter: 255
  • Heap Blocks: exact=38
  • Buffers: shared hit=54
14. 0.294 0.294 ↑ 1.0 256 6

Bitmap Index Scan on inddev_spr__pattern_idx (cost=0.00..3.20 rows=256 width=0) (actual time=0.049..0.049 rows=256 loops=6)

  • Index Cond: ((md5((spr1.pattern_id)::text))::uuid = p1.par5)
  • Buffers: shared hit=16
15. 0.038 0.038 ↑ 1.0 3 2

Index Scan using inddev_para_old5_idx on public.inddev_para p2 (cost=0.56..5.01 rows=3 width=48) (actual time=0.010..0.019 rows=3 loops=2)

  • Output: p2.id, p2.old5, p2.new5, p2.par5
  • Index Cond: (p2.old5 = p1.old5)
  • Buffers: shared hit=14
16. 1.260 1.488 ↓ 0.0 0 6

Bitmap Heap Scan on public.inddev_spr spr2 (cost=3.20..31.08 rows=1 width=54) (actual time=0.136..0.248 rows=0 loops=6)

  • Output: spr2.id, spr2."values", spr2.pattern_id
  • Recheck Cond: ((md5((spr2.pattern_id)::text))::uuid = p2.par5)
  • Filter: ((spr2.pattern_id = 3) AND (p2.new5 = (md5((spr2.id)::text))::uuid))
  • Rows Removed by Filter: 255
  • Heap Blocks: exact=38
  • Buffers: shared hit=54
17. 0.228 0.228 ↑ 1.0 256 6

Bitmap Index Scan on inddev_spr__pattern_idx (cost=0.00..3.20 rows=256 width=0) (actual time=0.038..0.038 rows=256 loops=6)

  • Index Cond: ((md5((spr2.pattern_id)::text))::uuid = p2.par5)
  • Buffers: shared hit=16