[JP]OSS版PG-StromにおけるPostGIS関数GPU利用

[kzyoshimura]

OSS版PG-Stromにおいて、PostGIS関数でGPU利用されない状況です。
Docker環境を構築しており、さくらインターネットの高火力DoKにて実行しています。
※PostGIS関数未使用のSQLでGPU利用されることはdiscussions#892にて確認できています

実行計画を確認したSQLを記載いたします。ご助言いただけますと幸いです。
SQL①

EXPLAIN
WITH distance as (
	SELECT
		  ST_XMin(geom)::numeric as x
		, ST_XMax(geom)::numeric as y
		, st_distance(st_point(ST_XMin(geom),ST_YMin(geom)),st_point(ST_XMax(geom),ST_YMax(geom))) as distance
  FROM mesh6
UNION
  SELECT
		  ST_YMin(geom)::numeric as x
		, ST_YMax(geom)::numeric as y
		, st_distance(st_point(ST_XMin(geom),ST_YMin(geom)),st_point(ST_XMax(geom),ST_YMax(geom))) as distance
  FROM mesh6
)
SELECT
	x, y, MAX(distance)
FROM distance
GROUP BY x, y
;

結果①

                                                                                                                               QUERY PLAN                                                                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=294703721.75..295573987.67 rows=40000 width=72)
   Group Key: ((st_xmin((mesh6.geom)::box3d))::numeric), ((st_xmax((mesh6.geom)::box3d))::numeric)
   ->  Unique  (cost=294703721.75..295200787.99 rows=49706624 width=72)
         ->  Sort  (cost=294703721.75..294827988.31 rows=49706624 width=72)
               Sort Key: ((st_xmin((mesh6.geom)::box3d))::numeric), ((st_xmax((mesh6.geom)::box3d))::numeric), (st_distance(st_point(st_xmin((mesh6.geom)::box3d), st_ymin((mesh6.geom)::box3d)), st_point(st_xmax((mesh6.geom)::box3d), st_ymax((mesh6.geom)::box3d))))
               ->  Gather  (cost=1000.00..286310741.68 rows=49706624 width=72)
                     Workers Planned: 2
                     ->  Parallel Append  (cost=0.00..281339079.28 rows=20711094 width=72)
                           ->  Parallel Seq Scan on mesh6  (cost=0.00..140617761.91 rows=10355547 width=72)
                           ->  Parallel Seq Scan on mesh6 mesh6_1  (cost=0.00..140617761.91 rows=10355547 width=72)
(10 rows)

SQL②

EXPLAIN
SELECT
	m.name, y.youtoid
	, m.geom
FROM mesh6 m
INNER JOIN youto y
ON ST_Relate(m.geom, y.wkb_geometry, 'T********')
GROUP BY m.name, y.youtoid
;

結果②

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Group  (cost=13603730666179.95..13606490537867.65 rows=298239744 width=137)
   Group Key: m.name, y.youtoid
   ->  Gather Merge  (cost=13603730666179.95..13606487555470.21 rows=596479488 width=137)
         Workers Planned: 2
         ->  Group  (cost=13603730665179.93..13606418705937.12 rows=298239744 width=137)
               Group Key: m.name, y.youtoid
               ->  Sort  (cost=13603730665179.93..13604626678765.66 rows=358405434293 width=137)
                     Sort Key: m.name, y.youtoid
                     ->  Nested Loop  (cost=0.00..13483496861752.50 rows=358405434293 width=137)
                           Join Filter: st_relate(m.geom, y.wkb_geometry, 'T********'::text)
                           ->  Parallel Seq Scan on youto y  (cost=0.00..22995.62 rows=43262 width=2076)
                           ->  Seq Scan on mesh6 m  (cost=0.00..755744.12 rows=24853312 width=132)
(12 rows)

SQL③

EXPLAIN
SELECT
	m.name
	, y.youtoid
	, m.geom
FROM mesh6 m
INNER JOIN youto y
ON st_contains(y.wkb_geometry, m.geom)
GROUP BY m.name, y.youtoid
;

結果③

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Group  (cost=1054.75..1423379923.43 rows=298239744 width=137)
   Group Key: m.name, y.youtoid
   ->  Gather Merge  (cost=1054.75..1420397525.99 rows=596479488 width=137)
         Workers Planned: 2
         ->  Group  (cost=54.73..1351547992.90 rows=298239744 width=137)
               Group Key: m.name, y.youtoid
               ->  Incremental Sort  (cost=54.73..1348188548.11 rows=671888959 width=137)
                     Sort Key: m.name, y.youtoid
                     Presorted Key: m.name
                     ->  Nested Loop  (cost=0.84..1323312955.84 rows=671888959 width=137)
                           ->  Parallel Index Scan using mesh6_pkey on mesh6 m  (cost=0.56..17919863.75 rows=10355547 width=132)
                           ->  Index Scan using youto_wkb_geometry_geom_idx on youto y  (cost=0.28..125.96 rows=10 width=2076)
                                 Index Cond: (wkb_geometry ~ m.geom)
                                 Filter: st_contains(wkb_geometry, m.geom)
(14 rows)

SQL④　※本来PG-Stromの効果を確認したかったもの
※JOINのON句ではST_Intersects(m.geom, y.wkb_geometry)を使用したかったのですが、PG-Stromが未対応のためST_Relate(m.geom, y.wkb_geometry, 'T********')を利用しています

EXPLAIN ANALYZE
CREATE TABLE mesh6_youto AS
WITH overlap_areas AS (
	SELECT
		m.name
		, y.youtoid
		, SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area
		, m.geom
	FROM mesh6 m
	INNER JOIN youto y
	ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry, 'T********')
	GROUP BY m.name, y.youtoid
), ranked AS (
	SELECT
		*
		, RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk
	FROM overlap_areas
)
SELECT
	name
	, youtoid
	, overlap_area
	, geom
FROM ranked
WHERE rnk = 1
;

結果④

                                                                                              QUERY PLAN                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on ranked  (cost=35862913.03..7185793621.33 rows=1491200 width=145) (actual time=218.199..925655.845 rows=1628627 loops=1)
   Filter: (ranked.rnk = 1)
   ->  WindowAgg  (cost=35862913.03..7182065620.33 rows=298240080 width=153) (actual time=218.197..925228.073 rows=1628627 loops=1)
         Run Condition: (rank() OVER (?) <= 1)
         ->  Incremental Sort  (cost=35862913.03..7176100818.73 rows=298240080 width=145) (actual time=218.187..922167.121 rows=2430085 loops=1)
               Sort Key: m.name, (sum(st_area((st_intersection(m.geom, y.wkb_geometry, '-1'::double precision))::geography, true))) DESC, y.youtoid
               Presorted Key: m.name
               Full-sort Groups: 74983  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB
               ->  Finalize GroupAggregate  (cost=1054.23..7141791212.23 rows=298240080 width=145) (actual time=203.653..917621.150 rows=2430085 loops=1)
                     Group Key: m.name, y.youtoid
                     ->  Gather Merge  (cost=1054.23..7135359991.73 rows=459842626 width=145) (actual time=203.530..914573.601 rows=2430085 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  Partial GroupAggregate  (cost=54.21..7082281742.82 rows=229921313 width=145) (actual time=101.390..644712.180 rows=810028 loops=3)
                                 Group Key: m.name, y.youtoid
                                 ->  Incremental Sort  (cost=54.21..1329650491.56 rows=229921313 width=2168) (actual time=56.734..413659.586 rows=872762 loops=3)
                                       Sort Key: m.name, y.youtoid
                                       Presorted Key: m.name
                                       Full-sort Groups: 16632  Sort Method: quicksort  Average Memory: 245kB  Peak Memory: 256kB
                                       Worker 0:  Full-sort Groups: 31861  Sort Method: quicksort  Average Memory: 253kB  Peak Memory: 257kB
                                       Worker 1:  Full-sort Groups: 31982  Sort Method: quicksort  Average Memory: 249kB  Peak Memory: 257kB
                                       ->  Nested Loop  (cost=0.84..1322589598.29 rows=229921313 width=2168) (actual time=36.381..410811.078 rows=872762 loops=3)
                                             ->  Parallel Index Scan using mesh6_pkey on mesh6 m  (cost=0.56..17920007.83 rows=10355558 width=132) (actual time=1.444..15170.097 rows=8284437 loops=3)
                                             ->  Index Scan using youto_wkb_geometry_geom_idx on youto y  (cost=0.28..125.96 rows=3 width=2036) (actual time=0.043..0.047 rows=0 loops=24853312)
                                                   Index Cond: (wkb_geometry && m.geom)
                                                   Filter: st_relate(m.geom, wkb_geometry, 'T********'::text)
                                                   Rows Removed by Filter: 0
 Planning Time: 2.109 ms
 Execution Time: 928871.167 ms
(29 rows)

[kaigai]

SQL①　mesh6テーブルに検索条件が付加されていないため、「件数の減る」部分ではなく、GPUを用いた実行計画を登録していない可能性があります。
SQL②　mesh6とyoutoテーブルのJOINの結果、行数が大幅に膨らんでいるため、GPU向きではないと判断し実行計画の登録を行っていないように見えます。
SQL③　これもJOINで行数の膨らむパターンですね
SQL④　最深部のNested LoopでJOIN結果の行数が膨らんでいるパターンです。ただ、実際の値を見てみると件数を大幅に絞り込んでいるため、本来的にはPostgreSQLのコスト推定（行数推定）がタコという事に見えます。

あとは、SET client_min_message = DEBUG2 と指定すると、PG-Stromを選択しない場合の理由がメッセージ出力される場合がありますので、それがヒントになる事もあります。

[kzyoshimura]

ご回答ありがとうございます！
コメントをいただき思考錯誤しましたがGPU利用できず、私の理解が及んでおりません。
こちらsakaikの日々雑感～(T)編 のサイトにてご紹介されているPostGISのSQLはGPU利用できました。

SELECT ST_Distance(ST_MakePoint(137.0, 44.0), ST_MakePoint(x,y)) FROM points1000man
    WHERE ST_Distance(ST_MakePoint(137.0, 44.0), ST_MakePoint(x,y))<0.5;

                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1100.00..22256778.06 rows=3848070 width=8)
   Workers Planned: 2
   ->  Parallel Custom Scan (GpuScan) on points1000man  (cost=100.00..21870971.06 rows=1603362 width=8)
         GPU Projection: st_distance('010100000000000000002061400000000000004640'::geometry, st_makepoint(x, y))
         GPU Scan Quals: ((st_distance('010100000000000000002061400000000000004640'::geometry, st_makepoint(x, y))) < '0.5'::double precision) [rows: 11544210 -> 1603362]
(5 rows)

しかしながら、本来は前述させていただきましたSQL④のような処理を実行したいのですが、これはOSS版ではGiSTが有効とならないことが原因でしょうか？有償版では結果が異なりますでしょうか？もしわかれば教えてください。
その他、GPU利用が有効となるSQL条件やデータ量などがございましたらご教示いただけますと幸いです。
※私がPG-Stromを理解できておらず申し訳ございませんが何卒よろしくお願いいたします

[kaigai]

コスト計算の問題でNestedLoopに負けているだけだと思うのですが、例えば
SET enable_nestloop = off
でNestedLoopのコストを抑えめにしてみるなどはどうでしょうか？

見た感じ、Join -> Agg -> Sort -> Window関数 という流れなので、ほぼ全部GPUでの実行も可能に思えるのですが…。

あとは、
SET client_min_message = debug2
で、何かGPU利用を阻害する要因がある場合はそれを表示してくれる事もあるかもしれません。

[kzyoshimura]

ご回答ありがとうございます。
OSS版でなく有償版でも結果は変わらないと認識いたしました。
SET enable_nestloop = off;を行ってもGPU利用されませんでした。
また、SET client_min_messages = DEBUG2; でも情報変わらない状況です。
もう少し探ってみようと思います。

[kzyoshimura]

SET client_min_messages = DEBUG2;にて情報出力されていました。すみません。
そもそも私のSQLに問題がありました。
PG-Stromはboolst_relate(geometry,geometry,text)には未対応でtext st_relate(geometry,geometry)を使用すべきでした。
SQLを次の通り修正いたしました。

EXPLAIN
CREATE TABLE mesh6_youto AS
WITH overlap_areas AS (
	SELECT
		m.name,
		y.youtoid,
		SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area,
		m.geom
	FROM mesh6 m
	INNER JOIN youto y 
	ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'
	GROUP BY m.name, y.youtoid
),
ranked AS (
	SELECT *,
		RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk
	FROM overlap_areas
)
SELECT name, youtoid, overlap_area, geom
FROM ranked
WHERE rnk = 1
;

すると実行計画では次の通りGPU利用されることが確認できました。

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on ranked  (cost=19035830.73..107795969.75 rows=41840 width=145)
   Filter: (ranked.rnk = 1)
   ->  WindowAgg  (cost=19035830.73..107691370.48 rows=8367942 width=153)
         Run Condition: (rank() OVER (?) <= 1)
         ->  Incremental Sort  (cost=19035830.73..107524011.64 rows=8367942 width=145)
               Sort Key: m.name, (sum(st_area((st_intersection(m.geom, y.wkb_geometry, '-1'::double precision))::geography, true))) DESC, y.youtoid
               Presorted Key: m.name
               ->  Finalize GroupAggregate  (cost=18591692.15..106777060.65 rows=8367942 width=145)
                     Group Key: m.name, y.youtoid
                     ->  Gather Merge  (cost=18591692.15..106641081.60 rows=6973284 width=145)
                           Workers Planned: 2
                           ->  Partial GroupAggregate  (cost=18590692.13..105835191.57 rows=3486642 width=145)
                                 Group Key: m.name, y.youtoid
                                 ->  Sort  (cost=18590692.13..18599408.73 rows=3486642 width=2223)
                                       Sort Key: m.name, y.youtoid
                                       ->  Parallel Custom Scan (GpuJoin) on mesh6 m  (cost=26949.86..14863065.08 rows=3486642 width=2223)
                                             GPU Projection: m.name, y.youtoid, m.geom, y.wkb_geometry
                                             GPU Join Quals [1]: (m.geom && y.wkb_geometry), (st_relate(m.geom, y.wkb_geometry) ~~ '2%'::text) ... [nrows: 10355560 -> 3486642]
                                             GPU GiST Join [1]: (y.wkb_geometry && m.geom) on youto_wkb_geometry_geom_idx (wkb_geometry)
                                             ->  Parallel Seq Scan on youto y  (cost=0.00..25450.62 rows=43262 width=2091)
(20 rows)

しかし、実行計画確認でなく、実際にSQLを実行させるとエラーが発生する状況です(数度実行したところエラーパターンは2つのようです)。何か解決策はございますでしょうか？
SQL　※SQL自体はGPUなしPCにて動作することは確認できております

EXPLAIN ANALYZE
CREATE TABLE mesh6_youto AS
WITH overlap_areas AS (
	SELECT
		m.name,
		y.youtoid,
		SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area,
		m.geom
	FROM mesh6 m
	INNER JOIN youto y 
	ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'
	GROUP BY m.name, y.youtoid
),
ranked AS (
	SELECT *,
		RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk
	FROM overlap_areas
)
SELECT name, youtoid, overlap_area, geom
FROM ranked
WHERE rnk = 1
;

ログ①

2025-03-30 13:10:53.783 UTC [37] LOG:  PG-Strom fatbin image is ready: pgstrom-gpucode-V012020-9c25655bc92c5b643beef5f536256a4a.fatbin
2025-03-30 13:10:54.529 UTC [37] LOG:  GPU0 workers - 13 startup (with GpuCacheManager), 0 terminate
2025-03-30 13:12:04.089 UTC [428] ERROR:  could not open file "base/16384/39880": Interrupted system call
2025-03-30 13:12:04.089 UTC [428] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:12:04.311 UTC [429] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-03-30 13:12:04.311 UTC [429] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:12:04.311 UTC [430] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-03-30 13:12:04.311 UTC [430] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:12:04.318 UTC [30] LOG:  background worker "parallel worker" (PID 429) exited with exit code 1
2025-03-30 13:12:04.320 UTC [30] LOG:  background worker "parallel worker" (PID 430) exited with exit code 1

ログ②

2025-03-30 13:20:27.895 UTC [37] LOG:  PG-Strom fatbin image is ready: pgstrom-gpucode-V012020-9c25655bc92c5b643beef5f536256a4a.fatbin
2025-03-30 13:20:28.687 UTC [37] LOG:  GPU0 workers - 13 startup (with GpuCacheManager), 0 terminate
2025-03-30 13:21:26.445 UTC [37] LOG:  GPU0: CUDA stack size expanded 0 -> 4096 bytes
2025-03-30 13:21:27.602 UTC [429] ERROR:  gpu_service.c:2813  failed on cuEventSynchronize: CUDA_ERROR_ILLEGAL_ADDRESS
2025-03-30 13:21:27.602 UTC [429] HINT:  device at GPU-0, function at gpuservHandleGpuTaskExec
2025-03-30 13:21:27.602 UTC [429] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:21:27.602 UTC [427] ERROR:  gpu_service.c:2813  failed on cuEventSynchronize: CUDA_ERROR_ILLEGAL_ADDRESS
2025-03-30 13:21:27.602 UTC [427] HINT:  device at GPU-0, function at gpuservHandleGpuTaskExec
2025-03-30 13:21:27.602 UTC [427] CONTEXT:  parallel worker
2025-03-30 13:21:27.602 UTC [427] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:21:27.629 UTC [30] LOG:  background worker "parallel worker" (PID 429) exited with exit code 1
2025-03-30 13:21:27.648 UTC [430] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-03-30 13:21:27.648 UTC [430] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; SET enable_nestloop = off; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS (   SELECT     m.name     , y.youtoid     , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area     , m.geom   FROM mesh6 m   INNER JOIN youto y   ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'   GROUP BY m.name, y.youtoid ), ranked AS (   SELECT     *     , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk   FROM overlap_areas ) SELECT   name   , youtoid   , overlap_area   , geom FROM ranked WHERE rnk = 1;
2025-03-30 13:21:27.685 UTC [30] LOG:  background worker "parallel worker" (PID 430) exited with exit code 1

[kaigai]

すいません。お返事を見落としておりました。

こちら単純にcuda_postgis.cuのバグだと思うのですが、再現パターン（データ）をご提供いただくことは可能でしょうか？
まずはクラッシュした地点を特定しないと直しようがありませんので、当方の手元でクラッシュダンプを取れるようにする
というのが問題解析のスタートになります。

[kzyoshimura]

ご連絡ありがとうございます。
Dockerでもよろしければ、こちらでデータ取得いただけますでしょうか？
docker pull kzyoshimura/pg-strom-postgis-pg16-cuda12.3:v2.2.1
postgresqlのポートは5432です。
次のコマンドにてDB起動ください。
pg_ctl -D "$PGDATA" -w start
postgresqlはユーザ：postgres、パスワード：postgres　です。
DB「test」に mesh6、youto テーブルがあります。
※PG-Strom v5.2.2にて試しており、最新の v6.0 ではまだ試せていない状況です

[kzyoshimura]

PG-Strom v6.0にて試してみましたが、こちらはエラーとなりませんでしたが処理が終わらない状況が発生します。
(手持ちのPCではCPU実行で約6分で終了します)
・SQL

EXPLAIN ANALYZE
CREATE TABLE mesh6_youto AS
WITH overlap_areas AS (
	SELECT
		m.name,
		y.youtoid,
		SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area,
		m.geom
	FROM mesh6 m
	INNER JOIN youto y 
	ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'
	GROUP BY m.name, y.youtoid
),
ranked AS (
	SELECT *,
		RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk
	FROM overlap_areas
)
SELECT name, youtoid, overlap_area, geom
FROM ranked
WHERE rnk = 1
;

・ログ

CUDA Version 12.3.2

Container image Copyright (c) 2016-2023, NVIDIA CORPORATION & AFFILIATES. All rights reserved.

This container image and its contents are governed by the NVIDIA Deep Learning Container License.
By pulling and using the container, you accept the terms and conditions of this license:
https://developer.nvidia.com/ngc/nvidia-deep-learning-container-license

A copy of this license is made available in this container at /NGC-DL-CONTAINER-LICENSE for your convenience.

waiting for server to start....2025-04-11 15:05:24.957 UTC [29] LOG:  HeteroDB Extra module loaded [api_version=20250316,cufile=on,nvme_strom=off,nvidia-fs=off,githash=e94d490f86848ec367843312481e0d257c5e64f3]
2025-04-11 15:05:24.957 UTC [29] LOG:  heterodb-extra: [error] failed on fopen('/etc/heterodb.license'): No such file or directory
	 (license.c:846)
2025-04-11 15:05:24.957 UTC [29] LOG:  PG-Strom version 6.0.2.el8 built for PostgreSQL 16 (githash: )
2025-04-11 15:05:25.043 UTC [29] LOG:  PG-Strom binary built for CUDA 12.6 (CUDA runtime 12.2)
2025-04-11 15:05:25.044 UTC [29] LOG:  PG-Strom: GPU0 Tesla V100-PCIE-32GB (80 SMs; 1380MHz, L2 6144kB), RAM 31.74GB (4096bits, 856MHz), PCI-E Bar1 32GB, CC 7.0
gpudirect_supported_gpus = 1
2025-04-11 15:05:25.045 UTC [29] LOG:  [0000:00:0a:0] GPU0 (Tesla V100-PCIE-32GB; GPU-a67ac87d-124f-c2dd-cd90-7b8585052ce1)
.2025-04-11 15:05:25.943 UTC [29] LOG:  redirecting log output to logging collector process
2025-04-11 15:05:25.943 UTC [29] HINT:  Future log output will appear in directory "log".
 done
server started
DEBUG:  analyzing "public.youto"
DEBUG:  "youto": scanned 25018 of 25018 pages, containing 103830 live rows and 0 dead rows; 30000 rows in sample, 103830 estimated total rows
DEBUG:  analyzing "public.mesh6"
DEBUG:  "mesh6": scanned 30000 of 507211 pages, containing 1470000 live rows and 0 dead rows; 30000 rows in sample, 24853339 estimated total rows
DEBUG:  rehashing catalog cache id 38 for pg_operator; 65 tups, 32 buckets
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  No alternative aggregation: {AGGREF :aggfnoid 2111 :aggtype 701 :aggcollid 0 :inputcollid 0 :aggtranstype 701 :aggargtypes (o 701) :aggdirectargs <> :args ({TARGETENTRY :expr {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :aggpresorted false :agglevelsup 0 :aggsplit 0 :aggno 0 :aggtransno 0 :location 212}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216}
DEBUG:  No alternative aggregation: {AGGREF :aggfnoid 2111 :aggtype 701 :aggcollid 0 :inputcollid 0 :aggtranstype 701 :aggargtypes (o 701) :aggdirectargs <> :args ({TARGETENTRY :expr {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 240} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 248} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 224}) :location 263} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 216} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :aggpresorted false :agglevelsup 0 :aggsplit 0 :aggno 0 :aggtransno 0 :location 212}
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  building index "pg_toast_38429_index" on table "pg_toast_38429" serially
DEBUG:  index "pg_toast_38429_index" can safely use deduplication
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
NOTICE:  GpuJoin: CPU fallback 364707 tuples (102.50MB)
NOTICE:  GpuJoin: CPU fallback 364706 tuples (77.20MB)
NOTICE:  GpuJoin: CPU fallback 364707 tuples (101.17MB)

ST_AreaなどPG-Strom未対応関数を使用しているため、それらを除外したSQLでも試しましたが状況かわりませんでした。
・SQL

SELECT 
    m.name,
    y.youtoid,
    m.geom,
    y.wkb_geometry
FROM mesh6 m
JOIN youto y
  ON m.geom && y.wkb_geometry 
AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%'
;

・ログ

DEBUG:  rehashing catalog cache id 38 for pg_operator; 65 tups, 32 buckets
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpusort: disabled by pg_strom.cpu_fallback
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
NOTICE:  GpuJoin: CPU fallback 364706 tuples (94.77MB)
NOTICE:  GpuJoin: CPU fallback 364706 tuples (131.15MB)
NOTICE:  GpuJoin: CPU fallback 364706 tuples (146.72MB)

何か解決策などございますでしょうか？

[kzyoshimura]

連投すみません。
set pg_strom.cpu_fallback = off;を試してみましたがエラーとなりましたのでログを残します。

2025-04-12 06:50:18.982 UTC [37] LOG:  PG-Strom fatbin image is ready: pgstrom-gpucode-V012020-21ed7d198b7906faf3401a3fceed94c3.fatbin
2025-04-12 06:50:19.773 UTC [37] LOG:  GPU0 workers - 17 startup (with GpuCacheManager), 0 terminate
2025-04-12 06:52:07.696 UTC [462] ERROR:  xpu_textlib.h:56  text datum is compressed or external
2025-04-12 06:52:07.696 UTC [462] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-12 06:52:07.698 UTC [466] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-04-12 06:52:07.698 UTC [466] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-12 06:52:07.700 UTC [465] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-04-12 06:52:07.700 UTC [465] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-12 06:52:07.730 UTC [30] LOG:  background worker "parallel worker" (PID 465) exited with exit code 1
2025-04-12 06:52:07.745 UTC [30] LOG:  background worker "parallel worker" (PID 466) exited with exit code 1

[kzyoshimura]

@kaigai
cudaを12.3.2→12.8.1に変更して試してみました。エラーとなる状況は変わりませんでしたが情報共有としてご連絡させていただきます。

CUDA Version 12.8.1

Container image Copyright (c) 2016-2023, NVIDIA CORPORATION & AFFILIATES. All rights reserved.

This container image and its contents are governed by the NVIDIA Deep Learning Container License.
By pulling and using the container, you accept the terms and conditions of this license:
https://developer.nvidia.com/ngc/nvidia-deep-learning-container-license

A copy of this license is made available in this container at /NGC-DL-CONTAINER-LICENSE for your convenience.

waiting for server to start....2025-04-29 12:12:53.616 UTC [28] LOG:  HeteroDB Extra module loaded [api_version=20250316,cufile=on,nvme_strom=off,nvidia-fs=off,githash=e94d490f86848ec367843312481e0d257c5e64f3]
2025-04-29 12:12:53.616 UTC [28] LOG:  heterodb-extra: [error] failed on fopen('/etc/heterodb.license'): No such file or directory
	 (license.c:846)
2025-04-29 12:12:53.616 UTC [28] LOG:  PG-Strom version 6.0.2.el8 built for PostgreSQL 16 (githash: )
2025-04-29 12:12:53.670 UTC [28] LOG:  PG-Strom binary built for CUDA 12.6 (CUDA runtime 12.2)
2025-04-29 12:12:53.670 UTC [28] LOG:  PG-Strom: GPU0 Tesla V100-PCIE-32GB (80 SMs; 1380MHz, L2 6144kB), RAM 31.74GB (4096bits, 856MHz), PCI-E Bar1 32GB, CC 7.0
gpudirect_supported_gpus = 1
2025-04-29 12:12:53.674 UTC [28] LOG:  [0000:00:0a:0] GPU0 (Tesla V100-PCIE-32GB; GPU-d05ef528-38ce-8c78-24fc-48a8bf82402b)
2025-04-29 12:12:53.964 UTC [28] LOG:  redirecting log output to logging collector process
2025-04-29 12:12:53.964 UTC [28] HINT:  Future log output will appear in directory "log".
 done
server started
DEBUG:  analyzing "public.youto"
DEBUG:  "youto": scanned 25064 of 25064 pages, containing 103830 live rows and 0 dead rows; 30000 rows in sample, 103830 estimated total rows
DEBUG:  analyzing "public.mesh6"
DEBUG:  "mesh6": scanned 30000 of 507264 pages, containing 1469853 live rows and 0 dead rows; 30000 rows in sample, 24853450 estimated total rows
DEBUG:  rehashing catalog cache id 38 for pg_operator; 65 tups, 32 buckets
DEBUG:  rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  No alternative aggregation: {AGGREF :aggfnoid 2111 :aggtype 701 :aggcollid 0 :inputcollid 0 :aggtranstype 701 :aggargtypes (o 701) :aggdirectargs <> :args ({TARGETENTRY :expr {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :aggpresorted false :agglevelsup 0 :aggsplit 0 :aggno 0 :aggtransno 0 :location 245}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  (__codegen_func_expression:1859) function st_area(geography,boolean) is not supported on the target device
DETAIL:  problematic expression: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  Partial aggregate argument is not executable: {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249}
DEBUG:  No alternative aggregation: {AGGREF :aggfnoid 2111 :aggtype 701 :aggcollid 0 :inputcollid 0 :aggtranstype 701 :aggargtypes (o 701) :aggdirectargs <> :args ({TARGETENTRY :expr {FUNCEXPR :funcid 17201 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 17116 :funcresulttype 17089 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 16811 :funcresulttype 16391 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 16391 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 273} {VAR :varno 2 :varattno 2 :vartype 16391 :vartypmod 1707032 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 281} {CONST :consttype 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 0 0 0 0 0 -16 -65 ]}) :location 257}) :location 296} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 1 0 0 0 0 0 0 0 ]}) :location 249} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :aggpresorted false :agglevelsup 0 :aggsplit 0 :aggno 0 :aggtransno 0 :location 245}
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  building index "pg_toast_37946_index" on table "pg_toast_37946" serially
DEBUG:  index "pg_toast_37946_index" can safely use deduplication
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  gpucache: table 'mesh6' is not configured - check row/statement triggers with pgstrom.gpucache_sync_trigger()
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
DEBUG:  probing availability of JIT provider at /usr/pgsql-16/lib/llvmjit.so
DEBUG:  provider not available, disabling JIT for current session
ERROR:  xpu_textlib.h:56  text datum is compressed or external

ポスグレログ

2025-04-29 12:14:38.794 UTC [35] LOG:  PG-Strom fatbin image is ready: pgstrom-gpucode-V012020-21ed7d198b7906faf3401a3fceed94c3.fatbin
2025-04-29 12:14:39.835 UTC [35] LOG:  GPU0 workers - 17 startup (with GpuCacheManager), 0 terminate
2025-04-29 12:16:37.780 UTC [462] ERROR:  xpu_textlib.h:56  text datum is compressed or external
2025-04-29 12:16:37.780 UTC [462] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-29 12:16:37.782 UTC [464] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-04-29 12:16:37.782 UTC [464] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-29 12:16:37.782 UTC [463] FATAL:  terminating background worker "parallel worker" due to administrator command
2025-04-29 12:16:37.782 UTC [463] STATEMENT:  CREATE EXTENSION pg_strom; SET pg_strom.enabled=on; SET client_min_messages = DEBUG2; set pg_strom.cpu_fallback = off; ANALYZE youto; ANALYZE mesh6; EXPLAIN ANALYZE CREATE TABLE mesh6_youto AS WITH overlap_areas AS ( SELECT m.name , y.youtoid , SUM(ST_Area(ST_Intersection(m.geom, y.wkb_geometry)::geography)) AS overlap_area , m.geom FROM mesh6 m INNER JOIN youto y ON m.geom && y.wkb_geometry AND ST_Relate(m.geom, y.wkb_geometry) LIKE '2%' GROUP BY m.name, y.youtoid ), ranked AS ( SELECT * , RANK() OVER (PARTITION BY name ORDER BY overlap_area DESC, youtoid) AS rnk FROM overlap_areas ) SELECT name , youtoid , overlap_area , geom FROM ranked WHERE rnk = 1;
2025-04-29 12:16:37.827 UTC [28] LOG:  background worker "parallel worker" (PID 463) exited with exit code 1
2025-04-29 12:16:37.828 UTC [28] LOG:  background worker "parallel worker" (PID 464) exited with exit code 1