Explain PostgreSQL / Plan Visualizer

2025-02-05 09:23:39.962

5559247425e150c140cb262045b00aae

1.705 ms

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

#19 MJ |

#30 FS |

tilemap | piechart

node, ms

tree, ms

rows

RRbF

loops

25′173.832

397

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

.384

25′173.832

Aggregate

CTE src

.001

-> Result

CTE arena

1.414

3.716

-> Aggregate

InitPlan 2 (returns $1)

.011

.012

-> CTE Scan on src

.901

2.290

2′500

-> Sort

.249

1.389

2′500

-> Nested Loop

.390

-> Function Scan on regexp_matches y

.750

2′500

-> Function Scan on regexp_split_to_table x

CTE path

21.670

21.686

20′000

-> Function Scan on regexp_matches p

InitPlan 4 (returns $4)

.016

-> CTE Scan on src src_1

CTE r

49.951

24′794.908

20′001

-> Recursive Union

.004

3.720

-> CTE Scan on arena

940.055

24′741.237

20′001

-> Nested Loop Left Join

14′220.711

23′641.182

20′001

-> Merge Join

60.003

20′001

-> Sort

20′001

-> WorkTable Scan on r

9′333.951

9′360.468

200′050′002

20′001

-> Sort

4.831

26.517

20′000

-> CTE Scan on path

20.000

160.000

20′000

-> Limit

140.000

20′000

0.0%

20′000

-> Function Scan on generate_series s

InitPlan 7 (returns $12)

.001

25′172.921

-> Limit

40.617

25′172.920

-> Sort

337.395

25′132.303

20′001

-> CTE Scan on r r_1

.527

25′173.448

603

397

39.7%

-> Function Scan on jsonb_each

.468

Planning Time

29.890

25′203.722

Execution Time

Aggregate (actual time=25′173.826..25′173.832 rows=1 loops=1)
  CTE src
    ->  Result (actual time=0.001..0.001 rows=1 loops=1)
  CTE arena
    ->  Aggregate (actual time=3.713..3.716 rows=1 loops=1)
          InitPlan 2 (returns $1)
            ->  CTE Scan on src (actual time=0.011..0.012 rows=1 loops=1)
          ->  Sort (actual time=2.176..2.290 rows=2′500 loops=1)
                Sort Key: x.x, y.y
                Sort Method: quicksort  Memory: 214kB
                ->  Nested Loop (actual time=0.403..1.389 rows=2′500 loops=1)
                      ->  Function Scan on regexp_matches y (actual time=0.386..0.390 rows=50 loops=1)
                      ->  Function Scan on regexp_split_to_table x (actual time=0.011..0.015 rows=50 loops=50)
  CTE path
    ->  Function Scan on regexp_matches p (actual time=13.220..21.686 rows=20′000 loops=1)
          InitPlan 4 (returns $4)
            ->  CTE Scan on src src_1 (actual time=0.015..0.016 rows=1 loops=1)
  CTE r
    ->  Recursive Union (actual time=3.727..24′794.908 rows=20′001 loops=1)
          ->  CTE Scan on arena (actual time=3.720..3.720 rows=1 loops=1)
          ->  Nested Loop Left Join (actual time=1.236..1.237 rows=1 loops=20′001)
                ->  Merge Join (actual time=1.182..1.182 rows=1 loops=20′001)
                      Merge Cond: (((r.i + 1)) = path.i)
                      ->  Sort (actual time=0.003..0.003 rows=1 loops=20′001)
                            Sort Key: ((r.i + 1))
                            Sort Method: quicksort  Memory: 40kB
                            ->  WorkTable Scan on r (actual time=0.000..0.000 rows=1 loops=20′001)
                      ->  Sort (actual time=0.001..0.468 rows=10′002 loops=20′001)
                            Sort Key: path.i
                            Sort Method: quicksort  Memory: 1706kB
                            ->  CTE Scan on path (actual time=13.221..26.517 rows=20′000 loops=1)
                ->  Limit (actual time=0.008..0.008 rows=1 loops=20′000)
                      ->  Function Scan on generate_series s (actual time=0.007..0.007 rows=1 loops=20′000)
                            Filter: ((r.map ->> ((r.bot + (path.d * point((s)::double precision, '0'::double precision))))::text) IS DISTINCT FROM 'O'::text)
                            Rows Removed by Filter: 0
  InitPlan 7 (returns $12)
    ->  Limit (actual time=25′172.921..25′172.921 rows=1 loops=1)
          ->  Sort (actual time=25′172.920..25′172.920 rows=1 loops=1)
                Sort Key: r_1.i DESC
                Sort Method: top-N heapsort  Memory: 70kB
                ->  CTE Scan on r r_1 (actual time=3.730..25′132.303 rows=20′001 loops=1)
  ->  Function Scan on jsonb_each (actual time=25′173.178..25′173.448 rows=603 loops=1)
        Filter: (value = '"O"'::jsonb)
        Rows Removed by Filter: 397
Planning Time: 0.468 ms
Execution Time: 25′203.722 ms

Process
  CTE src
    ->  Result
  CTE arena
    ->  Process
          InitPlan 2
            ->  Scan CTE src
          ->  Join
                ->  Scan Function regexp_matches
                ->  Scan Function regexp_split_to_table
  CTE path
    ->  Scan Function regexp_matches
          InitPlan 4
            ->  Scan CTE src
  CTE r
    ->  Recursive Union
          ->  Scan CTE arena
          ->  Join
                ->  Scan CTE path
                ->  Scan CTE r
                ->  Scan Function generate_series
  InitPlan 7
    ->  Limit
          ->  Scan CTE r
  ->  Scan Function jsonb_each

"Aggregate (actual time=25173.826..25173.832 rows=1 loops=1)"
"  CTE src"
"    ->  Result (actual time=0.001..0.001 rows=1 loops=1)"
"  CTE arena"
"    ->  Aggregate (actual time=3.713..3.716 rows=1 loops=1)"
"          InitPlan 2 (returns $1)"
"            ->  CTE Scan on src (actual time=0.011..0.012 rows=1 loops=1)"
"          ->  Sort (actual time=2.176..2.290 rows=2500 loops=1)"
"                Sort Key: x.x, y.y"
"                Sort Method: quicksort  Memory: 214kB"
"                ->  Nested Loop (actual time=0.403..1.389 rows=2500 loops=1)"
"                      ->  Function Scan on regexp_matches y (actual time=0.386..0.390 rows=50 loops=1)"
"                      ->  Function Scan on regexp_split_to_table x (actual time=0.011..0.015 rows=50 loops=50)"
"  CTE path"
"    ->  Function Scan on regexp_matches p (actual time=13.220..21.686 rows=20000 loops=1)"
"          InitPlan 4 (returns $4)"
"            ->  CTE Scan on src src_1 (actual time=0.015..0.016 rows=1 loops=1)"
"  CTE r"
"    ->  Recursive Union (actual time=3.727..24794.908 rows=20001 loops=1)"
"          ->  CTE Scan on arena (actual time=3.720..3.720 rows=1 loops=1)"
"          ->  Nested Loop Left Join (actual time=1.236..1.237 rows=1 loops=20001)"
"                ->  Merge Join (actual time=1.182..1.182 rows=1 loops=20001)"
"                      Merge Cond: (((r.i + 1)) = path.i)"
"                      ->  Sort (actual time=0.003..0.003 rows=1 loops=20001)"
"                            Sort Key: ((r.i + 1))"
"                            Sort Method: quicksort  Memory: 40kB"
"                            ->  WorkTable Scan on r (actual time=0.000..0.000 rows=1 loops=20001)"
"                      ->  Sort (actual time=0.001..0.468 rows=10002 loops=20001)"
"                            Sort Key: path.i"
"                            Sort Method: quicksort  Memory: 1706kB"
"                            ->  CTE Scan on path (actual time=13.221..26.517 rows=20000 loops=1)"
"                ->  Limit (actual time=0.008..0.008 rows=1 loops=20000)"
"                      ->  Function Scan on generate_series s (actual time=0.007..0.007 rows=1 loops=20000)"
"                            Filter: ((r.map ->> ((r.bot + (path.d * point((s)::double precision, '0'::double precision))))::text) IS DISTINCT FROM 'O'::text)"
"                            Rows Removed by Filter: 0"
"  InitPlan 7 (returns $12)"
"    ->  Limit (actual time=25172.921..25172.921 rows=1 loops=1)"
"          ->  Sort (actual time=25172.920..25172.920 rows=1 loops=1)"
"                Sort Key: r_1.i DESC"
"                Sort Method: top-N heapsort  Memory: 70kB"
"                ->  CTE Scan on r r_1 (actual time=3.730..25132.303 rows=20001 loops=1)"
"  ->  Function Scan on jsonb_each (actual time=25173.178..25173.448 rows=603 loops=1)"
"        Filter: (value = '"O"'::jsonb)"
"        Rows Removed by Filter: 397"
"Planning Time: 0.468 ms"
"Execution Time: 25203.722 ms"

Сводно

Сводно

время	%	rows	RRbF	%	loops	тип узла	таблица	узлы
14′220.711	56.5	20′001			20′001	Merge Join		19
9′435.472	37.5	200′072′504			40′004	Sort		7 20 22 28	4
940.055	3.7	20′001			20′001	Nested Loop Left Join		18
337.395	1.3	20′001				CTE Scan	r	29
140.000	0.6	20′000			20′000	Function Scan	generate_series	25
49.951	0.2	20′001				Recursive Union		16
22.060	0.1	20′050			2	Function Scan	regexp_matches	9 12	2
20.001	0.1	20′001			20′001	Limit		24 27	2
4.831	0.0	20′000				CTE Scan	path	23
1.798	0.0	2			2	Aggregate		0 4	2
.750	0.0	2′500			50	Function Scan	regexp_split_to_table	10
.527	0.0	603	397	39.7		Function Scan	jsonb_each	30
.249	0.0	2′500				Nested Loop		8
.027	0.0	2			2	CTE Scan	src	6 14	2
.004	0.0	1				CTE Scan	arena	17
.001	0.0	1				Result		2
		20′001			20′001	WorkTable Scan	r	21