explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YRbI

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 8,627.093 ↓ 2.0 2 1

Sort (cost=1,019,884.96..1,019,884.97 rows=1 width=573) (actual time=8,627.093..8,627.093 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=21664 read=393853
  • I/O Timings: read=2588.514
2. 0.104 8,627.073 ↓ 2.0 2 1

Nested Loop (cost=696,721.29..1,019,884.95 rows=1 width=573) (actual time=8,100.205..8,627.073 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=21664 read=393853
  • I/O Timings: read=2588.514
3. 1,200.777 8,626.285 ↓ 6.0 6 1

Hash Join (cost=696,718.09..1,019,853.86 rows=1 width=473) (actual time=6,153.000..8,626.285 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
  • Hash Cond: (p2.old5 = p1.old5)
  • Buffers: shared hit=21610 read=393853
  • I/O Timings: read=2588.514
4. 2,006.506 2,006.506 ↑ 1.0 13,430,208 1

Seq Scan on public.inddev_para p2 (cost=0.00..272,770.14 rows=13,430,814 width=48) (actual time=0.366..2,006.506 rows=13,430,208 loops=1)

  • Output: p2.id, p2.old5, p2.new5, p2.par5
  • Buffers: shared hit=7209 read=131253
  • I/O Timings: read=958.672
5. 0.008 5,419.002 ↓ 2.0 2 1

Hash (cost=696,718.08..696,718.08 rows=1 width=473) (actual time=5,419.002..5,419.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
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=14401 read=262600
  • I/O Timings: read=1629.842
6. 0.041 5,418.994 ↓ 2.0 2 1

Nested Loop (cost=373,550.86..696,718.08 rows=1 width=473) (actual time=3,323.871..5,418.994 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=14401 read=262600
  • I/O Timings: read=1629.842
7. 0.060 5,416.973 ↓ 6.0 6 1

Hash Join (cost=373,547.66..696,686.99 rows=1 width=459) (actual time=3,323.756..5,416.973 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
  • Hash Cond: ((p3.par5 = (md5((spr3.pattern_id)::text))::uuid) AND (p3.new5 = (md5((spr3.id)::text))::uuid))
  • Buffers: shared hit=14347 read=262600
  • I/O Timings: read=1629.842
8. 1,020.818 5,416.320 ↑ 1.2 18 1

Hash Join (cost=373,509.90..696,645.81 rows=22 width=445) (actual time=3,323.128..5,416.320 rows=18 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, p3.par5, p3.new5
  • Hash Cond: (p3.old5 = p1.old5)
  • Buffers: shared hit=14330 read=262600
  • I/O Timings: read=1629.842
9. 1,703.076 1,703.076 ↑ 1.0 13,430,208 1

Seq Scan on public.inddev_para p3 (cost=0.00..272,770.14 rows=13,430,814 width=48) (actual time=0.011..1,703.076 rows=13,430,208 loops=1)

  • Output: p3.id, p3.old5, p3.new5, p3.par5
  • Buffers: shared hit=7178 read=131284
  • I/O Timings: read=802.275
10. 0.019 2,692.426 ↓ 2.0 6 1

Hash (cost=373,509.86..373,509.86 rows=3 width=397) (actual time=2,692.426..2,692.426 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
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=7152 read=131316
  • I/O Timings: read=827.567
11. 997.390 2,692.407 ↓ 2.0 6 1

Hash Join (cost=8.59..373,509.86 rows=3 width=397) (actual time=695.645..2,692.407 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
  • Hash Cond: (p1.old5 = (md5((c.fio)::text))::uuid)
  • Buffers: shared hit=7152 read=131316
  • I/O Timings: read=827.567
12. 1,694.975 1,694.975 ↑ 1.0 13,430,208 1

Seq Scan on public.inddev_para p1 (cost=0.00..272,770.14 rows=13,430,814 width=48) (actual time=0.005..1,694.975 rows=13,430,208 loops=1)

  • Output: p1.id, p1.old5, p1.new5, p1.par5
  • Buffers: shared hit=7146 read=131316
  • I/O Timings: read=827.567
13. 0.007 0.042 ↓ 2.0 2 1

Hash (cost=8.57..8.57 rows=1 width=349) (actual time=0.042..0.042 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
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6
14. 0.035 0.035 ↓ 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.032..0.035 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
  • Index Cond: ((c.fio)::text = 'Ладомирский Валентин Назарович'::text)
  • Buffers: shared hit=6
15. 0.413 0.593 ↑ 1.0 104 1

Hash (cost=36.20..36.20 rows=104 width=54) (actual time=0.593..0.593 rows=104 loops=1)

  • Output: spr3."values", spr3.pattern_id, spr3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=17
16. 0.180 0.180 ↑ 1.0 104 1

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

  • Output: spr3."values", spr3.pattern_id, spr3.id
  • Filter: (spr3.pattern_id = 5)
  • Rows Removed by Filter: 1432
  • Buffers: shared hit=17
17. 1.890 1.980 ↓ 0.0 0 6

Bitmap Heap Scan on public.inddev_spr spr1 (cost=3.20..31.08 rows=1 width=54) (actual time=0.027..0.330 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
18. 0.090 0.090 ↑ 1.0 256 6

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

  • Index Cond: ((md5((spr1.pattern_id)::text))::uuid = p1.par5)
  • Buffers: shared hit=16
19. 0.570 0.684 ↓ 0.0 0 6

Bitmap Heap Scan on public.inddev_spr spr2 (cost=3.20..31.08 rows=1 width=54) (actual time=0.059..0.114 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
20. 0.114 0.114 ↑ 1.0 256 6

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

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