Explain PostgreSQL / Plan Visualizer

2025-03-17 14:13:26.095

5ab0293d7765269d8a605810534f6191

2.421 ms

сообщить о проблеме

tilemap | piechart

node, ms

tree, ms

rows

loops

node

123.261

итоговые результаты

.026

123.261

Aggregate

CTE numpad

.006

.031

-> Nested Loop

.013

-> Function Scan on regexp_matches y

.012

-> Function Scan on regexp_matches x

CTE arrpad

.002

.021

-> Nested Loop

.011

-> Function Scan on regexp_matches y_1

.008

-> Function Scan on regexp_matches x_1

CTE arrpad_step

.056

.195

-> HashSetOp Except All

.003

.139

-> Append

.004

.133

-> Subquery Scan on "*SELECT* 1"

.064

.129

-> HashAggregate

.010

.065

-> Result

.024

.055

-> ProjectSet

.004

.031

-> Nested Loop

.017

-> CTE Scan on arrpad kb1

.006

.010

-> CTE Scan on arrpad kb2

.001

.003

-> Subquery Scan on "*SELECT* 2"

.002

-> Values Scan on "*VALUES*"

.759

123.235

-> Unique

3.858

122.476

8′960

-> Sort

4.111

118.618

8′960

-> Nested Loop

.020

5.139

-> Nested Loop

.009

2.719

-> Nested Loop

.030

-> Function Scan on regexp_matches line

.015

2.680

-> Limit

CTE r

.030

2.635

-> Recursive Union

.005

-> Result

.430

2.600

-> Hash Join

.420

2.020

3′380

-> Subquery Scan on numpad_step

1.205

1.600

3′380

-> HashSetOp Except All

.015

.395

193

-> Append

.016

.374

181

-> Subquery Scan on "*SELECT* 1_1"

.142

.358

181

-> HashAggregate

.043

.216

242

-> Result

.110

.173

242

-> ProjectSet

.021

.063

121

-> Nested Loop

.011

.020

-> CTE Scan on numpad kb1_1

.022

121

-> CTE Scan on numpad kb2_1

.002

.006

-> Subquery Scan on "*SELECT* 2_1"

.004

-> Values Scan on "*VALUES*_1"

.025

.150

-> Hash

.075

.125

-> Hash Join

.025

100

-> Function Scan on generate_series i

.025

-> Hash

-> WorkTable Scan on r r_3

.025

2.665

-> Sort

.005

2.640

-> CTE Scan on r

.040

2.400

-> Memoize

.020

2.360

-> Subquery Scan on t1

.040

2.340

-> Limit

CTE r

.110

2.150

300

-> Recursive Union

-> Result

.594

2.040

272

136

-> Hash Join

.435

.630

3′654

126

-> CTE Scan on arrpad_step

.272

.816

272

136

-> Hash

.272

.544

272

136

-> Hash Join

.136

1′768

136

-> Function Scan on generate_series i_1

.136

272

136

-> Hash

272

136

-> WorkTable Scan on r r_4

.070

2.300

-> Sort

.080

2.230

300

-> CTE Scan on r r_1

2.088

109.368

8′928

-> Memoize

.936

107.280

8′928

-> Subquery Scan on t2

1.080

106.344

8′928

-> Limit

CTE r

9.048

87.264

49′608

-> Recursive Union

.072

-> Result

29.784

78.144

48′576

2′112

-> Hash Join

6.120

59′160

2′040

-> CTE Scan on arrpad_step arrpad_step_1

19.008

42.240

40′128

2′112

-> Hash

10.560

23.232

40′128

2′112

-> Hash Join

4.224

59′136

2′112

-> Function Scan on generate_series i_2

6.336

8.448

48′576

2′112

-> Hash

2.112

48′576

2′112

-> WorkTable Scan on r r_5

5.904

105.264

9′000

-> Sort

12.096

99.360

49′608

-> CTE Scan on r r_2

.981

Planning Time

1.036

124.297

Execution Time

Aggregate (actual time=123.240..123.261 rows=1 loops=1)
  CTE numpad
    ->  Nested Loop (actual time=0.018..0.031 rows=11 loops=1)
          ->  Function Scan on regexp_matches y (actual time=0.012..0.013 rows=4 loops=1)
          ->  Function Scan on regexp_matches x (actual time=0.003..0.003 rows=3 loops=4)
                Filter: (d[1] <> ' '::text)
                Rows Removed by Filter: 0
  CTE arrpad
    ->  Nested Loop (actual time=0.016..0.021 rows=5 loops=1)
          ->  Function Scan on regexp_matches y_1 (actual time=0.010..0.011 rows=2 loops=1)
          ->  Function Scan on regexp_matches x_1 (actual time=0.003..0.004 rows=2 loops=2)
                Filter: (d[1] <> ' '::text)
                Rows Removed by Filter: 0
  CTE arrpad_step
    ->  HashSetOp Except All (actual time=0.144..0.195 rows=29 loops=1)
          ->  Append (actual time=0.079..0.139 rows=37 loops=1)
                ->  Subquery Scan on "*SELECT* 1" (actual time=0.078..0.133 rows=33 loops=1)
                      ->  HashAggregate (actual time=0.078..0.129 rows=33 loops=1)
                            Group Key: (kb1.k || kb2.k), ((unnest(ARRAY[(repeat(CASE WHEN (kb2.x > kb1.x) THEN '>'::text ELSE '<'::text END, (abs((kb2.x - kb1.x)))::integer) || repeat(CASE WHEN (kb2.y > kb1.y) THEN 'v'::text ELSE '^'::text END, (abs((kb2.y - kb1.y)))::integer)), (repeat(CASE WHEN (kb2.y > kb1.y) THEN 'v'::text ELSE '^'::text END, (abs((kb2.y - kb1.y)))::integer) || repeat(CASE WHEN (kb2.x > kb1.x) THEN '>'::text ELSE '<'::text END, (abs((kb2.x - kb1.x)))::integer))])) || 'A'::text)
                            Batches: 1  Memory Usage: 409kB
                            ->  Result (actual time=0.021..0.065 rows=50 loops=1)
                                  ->  ProjectSet (actual time=0.020..0.055 rows=50 loops=1)
                                        ->  Nested Loop (actual time=0.017..0.031 rows=25 loops=1)
                                              ->  CTE Scan on arrpad kb1 (actual time=0.016..0.017 rows=5 loops=1)
                                              ->  CTE Scan on arrpad kb2 (actual time=0.000..0.002 rows=5 loops=5)
                ->  Subquery Scan on "*SELECT* 2" (actual time=0.001..0.003 rows=4 loops=1)
                      ->  Values Scan on "*VALUES*" (actual time=0.001..0.002 rows=4 loops=1)
  ->  Unique (actual time=122.009..123.235 rows=5 loops=1)
        ->  Sort (actual time=122.008..122.476 rows=8′960 loops=1)
              Sort Key: (line.line[1]), (length(t2.code2))
              Sort Method: quicksort  Memory: 1319kB
              ->  Nested Loop (actual time=2.484..118.618 rows=8′960 loops=1)
                    ->  Nested Loop (actual time=1.245..5.139 rows=72 loops=1)
                          ->  Nested Loop (actual time=0.876..2.719 rows=10 loops=1)
                                ->  Function Scan on regexp_matches line (actual time=0.028..0.030 rows=5 loops=1)
                                ->  Limit (actual time=0.534..0.536 rows=2 loops=5)
                                      CTE r
                                        ->  Recursive Union (actual time=0.001..0.527 rows=7 loops=5)
                                              ->  Result (actual time=0.000..0.001 rows=1 loops=5)
                                              ->  Hash Join (actual time=0.062..0.104 rows=1 loops=25)
                                                    Hash Cond: (numpad_step.pair = substr(('A'::text || (line.line)[1]), i.i, 2))
                                                    ->  Subquery Scan on numpad_step (actual time=0.022..0.101 rows=169 loops=20)
                                                          ->  HashSetOp Except All (actual time=0.022..0.080 rows=169 loops=20)
                                                                ->  Append (actual time=0.290..0.395 rows=193 loops=1)
                                                                      ->  Subquery Scan on "*SELECT* 1_1" (actual time=0.289..0.374 rows=181 loops=1)
                                                                            ->  HashAggregate (actual time=0.289..0.358 rows=181 loops=1)
                                                                                  Group Key: (kb1_1.k || kb2_1.k), ((unnest(ARRAY[(repeat(CASE WHEN (kb2_1.x > kb1_1.x) THEN '>'::text ELSE '<'::text END, (abs((kb2_1.x - kb1_1.x)))::integer) || repeat(CASE WHEN (kb2_1.y > kb1_1.y) THEN 'v'::text ELSE '^'::text END, (abs((kb2_1.y - kb1_1.y)))::integer)), (repeat(CASE WHEN (kb2_1.y > kb1_1.y) THEN 'v'::text ELSE '^'::text END, (abs((kb2_1.y - kb1_1.y)))::integer) || repeat(CASE WHEN (kb2_1.x > kb1_1.x) THEN '>'::text ELSE '<'::text END, (abs((kb2_1.x - kb1_1.x)))::integer))])) || 'A'::text)
                                                                                  Batches: 1  Memory Usage: 409kB
                                                                                  ->  Result (actual time=0.025..0.216 rows=242 loops=1)
                                                                                        ->  ProjectSet (actual time=0.025..0.173 rows=242 loops=1)
                                                                                              ->  Nested Loop (actual time=0.020..0.063 rows=121 loops=1)
                                                                                                    ->  CTE Scan on numpad kb1_1 (actual time=0.019..0.020 rows=11 loops=1)
                                                                                                    ->  CTE Scan on numpad kb2_1 (actual time=0.000..0.002 rows=11 loops=11)
                                                                      ->  Subquery Scan on "*SELECT* 2_1" (actual time=0.001..0.006 rows=12 loops=1)
                                                                            ->  Values Scan on "*VALUES*_1" (actual time=0.001..0.004 rows=12 loops=1)
                                                    ->  Hash (actual time=0.006..0.006 rows=1 loops=25)
                                                          Buckets: 1′024  Batches: 1  Memory Usage: 9kB
                                                          ->  Hash Join (actual time=0.004..0.005 rows=1 loops=25)
                                                                Hash Cond: (i.i = r_3.i)
                                                                ->  Function Scan on generate_series i (actual time=0.001..0.001 rows=4 loops=25)
                                                                ->  Hash (actual time=0.001..0.001 rows=1 loops=25)
                                                                      Buckets: 1′024  Batches: 1  Memory Usage: 9kB
                                                                      ->  WorkTable Scan on r r_3 (actual time=0.000..0.000 rows=1 loops=25)
                                      ->  Sort (actual time=0.533..0.533 rows=3 loops=5)
                                            Sort Key: r.i DESC
                                            Sort Method: quicksort  Memory: 25kB
                                            ->  CTE Scan on r (actual time=0.002..0.528 rows=7 loops=5)
                          ->  Memoize (actual time=0.232..0.240 rows=7 loops=10)
                                Cache Key: r.code0, r.code0
                                Cache Mode: binary
                                Hits: 0  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 6kB
                                ->  Subquery Scan on t1 (actual time=0.231..0.236 rows=7 loops=10)
                                      ->  Limit (actual time=0.230..0.234 rows=7 loops=10)
                                            CTE r
                                              ->  Recursive Union (actual time=0.000..0.215 rows=30 loops=10)
                                                    ->  Result (actual time=0.000..0.000 rows=1 loops=10)
                                                    ->  Hash Join (actual time=0.011..0.015 rows=2 loops=136)
                                                          Hash Cond: (arrpad_step.pair = substr(('A'::text || r.code0), i_1.i, 2))
                                                          ->  CTE Scan on arrpad_step (actual time=0.001..0.005 rows=29 loops=126)
                                                          ->  Hash (actual time=0.006..0.006 rows=2 loops=136)
                                                                Buckets: 1′024  Batches: 1  Memory Usage: 9kB
                                                                ->  Hash Join (actual time=0.003..0.004 rows=2 loops=136)
                                                                      Hash Cond: (i_1.i = r_4.i)
                                                                      ->  Function Scan on generate_series i_1 (actual time=0.000..0.001 rows=13 loops=136)
                                                                      ->  Hash (actual time=0.001..0.001 rows=2 loops=136)
                                                                            Buckets: 1′024  Batches: 1  Memory Usage: 10kB
                                                                            ->  WorkTable Scan on r r_4 (actual time=0.000..0.000 rows=2 loops=136)
                                            ->  Sort (actual time=0.229..0.230 rows=8 loops=10)
                                                  Sort Key: r_1.i DESC
                                                  Sort Method: quicksort  Memory: 27kB
                                                  ->  CTE Scan on r r_1 (actual time=0.001..0.223 rows=30 loops=10)
                    ->  Memoize (actual time=1.458..1.519 rows=124 loops=72)
                          Cache Key: t1.code1, t1.code1
                          Cache Mode: binary
                          Hits: 0  Misses: 72  Evictions: 0  Overflows: 0  Memory Usage: 918kB
                          ->  Subquery Scan on t2 (actual time=1.457..1.490 rows=124 loops=72)
                                ->  Limit (actual time=1.456..1.477 rows=124 loops=72)
                                      CTE r
                                        ->  Recursive Union (actual time=0.001..1.212 rows=689 loops=72)
                                              ->  Result (actual time=0.001..0.001 rows=1 loops=72)
                                              ->  Hash Join (actual time=0.024..0.037 rows=23 loops=2′112)
                                                    Hash Cond: (arrpad_step_1.pair = substr(('A'::text || t1.code1), i_2.i, 2))
                                                    ->  CTE Scan on arrpad_step arrpad_step_1 (actual time=0.000..0.003 rows=29 loops=2′040)
                                                    ->  Hash (actual time=0.020..0.020 rows=19 loops=2′112)
                                                          Buckets: 1′024  Batches: 1  Memory Usage: 36kB
                                                          ->  Hash Join (actual time=0.007..0.011 rows=19 loops=2′112)
                                                                Hash Cond: (i_2.i = r_5.i)
                                                                ->  Function Scan on generate_series i_2 (actual time=0.000..0.002 rows=28 loops=2′112)
                                                                ->  Hash (actual time=0.004..0.004 rows=23 loops=2′112)
                                                                      Buckets: 1′024  Batches: 1  Memory Usage: 35kB
                                                                      ->  WorkTable Scan on r r_5 (actual time=0.000..0.001 rows=23 loops=2′112)
                                      ->  Sort (actual time=1.456..1.462 rows=125 loops=72)
                                            Sort Key: r_2.i DESC
                                            Sort Method: quicksort  Memory: 160kB
                                            ->  CTE Scan on r r_2 (actual time=0.003..1.380 rows=689 loops=72)
Planning Time: 0.981 ms
Execution Time: 124.297 ms

Сводно

Сводно

время	%	rows	loops	тип узла	таблица	узлы
41.715	33.8	89′298	4′546	Hash Join		31 45 57 60 72 75	6
25.802	20.9	89′298	4′546	Hash		44 47 59 62 74 77	6
12.181	9.9	49′943	87	CTE Scan	r	50 65 80	3
9.857	8.0	18′055	88	Sort		22 49 64 79	4
9.188	7.5	49′943	87	Recursive Union		29 55 70	3
6.555	5.3	62′814	2′166	CTE Scan	arrpad_step	58 73	2
4.385	3.6	61′004	2′273	Function Scan	generate_series	46 61 76	3
4.173	3.4	9′204	7	Nested Loop		2 6 16 23 24 25 39	7
2.128	1.7	8′998	82	Memoize		51 66	2
2.112	1.7	48′873	2′273	WorkTable Scan	r	48 63 78	3
1.261	1.0	3′409	21	HashSetOp Except All		10 33	2
1.135	0.9	9′008	87	Limit		27 53 68	3
.936	0.8	8′928	72	Subquery Scan	t2	67
.759	0.6	5		Unique		21
.420	0.3	3′380	20	Subquery Scan	numpad_step	32
.206	0.2	214	2	HashAggregate		13 36	2
.134	0.1	292	2	ProjectSet		15 38	2
.130	0.1	379	89	Result		14 30 37 56 71	5
.074	0.1	27	9	Function Scan	regexp_matches	3 4 7 8 26	5
.026	0.0	1		Aggregate		0
.020	0.0	70	10	Subquery Scan	t1	52
.018	0.0	230	2	Append		11 34	2
.016	0.0	181		Subquery Scan	SELECT 1_1	35
.011	0.0	132	12	CTE Scan	numpad	40 41	2
.006	0.0	30	6	CTE Scan	arrpad	17 18	2
.004	0.0	33		Subquery Scan	SELECT 1	12
.004	0.0	12		Values Scan	VALUES_1	43
.002	0.0	12		Subquery Scan	SELECT 2_1	42
.002	0.0	4		Values Scan	VALUES	20
.001	0.0	4		Subquery Scan	SELECT 2	19