Comments (2)
修正しました。
各JoinRel (RelOptInfo) ごとに varnullingrels の付き方が違うので、複数段のJOINの場合、
より下位のJoinからpull-upしたexpression式についている varnullingrels の値が不一致でしたので、
expression式をパースして整合性のある値に付け替えました。
この問題、これで最後にしたい・・・。
sakaitest_nvme5=# explain analyze SELECT s.filename, s.surface_id, c.curve_id, p.xml_pid
FROM moj_surface s
LEFT OUTER JOIN moj_curves_data c ON (s.filename=c.filename AND s.curve_id=c.curve_id)
LEFT OUTER JOIN moj_points p ON (c.filename=p.filename AND c.xml_pid=p.xml_pid)
WHERE s.filename='43443-3300-22.zip';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=5866283.33..13070454.50 rows=867496 width=51) (actual time=42644.964..42668.072 rows=10918 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Custom Scan (GpuJoin) on moj_curves_data c (cost=5865283.33..12982704.90 rows=361457 width=51) (actual time=41957.340..41957.972 rows=3639 loops=3)
GPU Projection: s.filename, s.surface_id, c.curve_id, p.xml_pid
GPU Scan Quals: ((c.filename)::text = '43443-3300-22.zip'::text) [plan: 6456368000 -> 110122, exec: 6455800708 -> 11216]
GPU Left Outer Join Quals [1]: true, ((c.xml_pid)::text = (p.xml_pid)::text) ... [plan: 110122 -> 297316, exec: 11216 -> 11216]
GPU Outer Hash [1]: (c.xml_pid)::text
GPU Inner Hash [1]: (p.xml_pid)::text
GPU Right Outer Join Quals [2]: true, ((s.curve_id)::text = (c.curve_id)::text) ... [plan: 297316 -> 361457, exec: 11216 -> 10918]
GPU Outer Hash [2]: (c.curve_id)::text
GPU Inner Hash [2]: (s.curve_id)::text
GPU-Direct SQL: enabled (GPU-0; direct=66610692, ntuples=6455800708)
-> Parallel Custom Scan (GpuScan) on moj_points p (cost=100.00..2345155.69 rows=26512 width=29) (actual time=7482.088..7729.458 rows=974 loops=3)
GPU Projection: xml_pid, filename
GPU Scan Quals: ((filename)::text = '43443-3300-22.zip'::text) [plan: 1556197000 -> 26512, exec: 1556071844 -> 2921]
GPU-Direct SQL: enabled (GPU-0; direct=22104730, ntuples=1556071844)
-> Parallel Custom Scan (GpuScan) on moj_surface s (cost=100.00..3517870.01 rows=54908 width=40) (actual time=11193.591..11544.208 rows=1820 loops=3)
GPU Projection: filename, surface_id, curve_id
GPU Scan Quals: ((filename)::text = '43443-3300-22.zip'::text) [plan: 3194538000 -> 54908, exec: 3194536648 -> 5459]
GPU-Direct SQL: enabled (GPU-0; direct=32933370, ntuples=3194536648)
Planning Time: 2.891 ms
Execution Time: 42668.612 ms
(23 rows)
from pg-strom.
報告したクエリが正常に動作するようになったことを確認しました。
PG-Strom off: 4 min 50 sec → PG-Strom on: 52 sec.
確認バージョン: 2943488800592106d67a0ade6e7ab80069dc0f56
from pg-strom.
Related Issues (20)
- assertion failure at TPC-H Q19 HOT 1
- Java liquibase throws max_num_rows consumes too much GPU device memory HOT 12
- [JP][document bug] ソースビルドの部分に記述ミスがある HOT 3
- [JP] CUDA 12.5の組み合わせで動作しない? HOT 2
- make install doesn't work HOT 2
- Arrowファイルを読み込ませてパーティション定義を作る関数 HOT 2
- Single Inner Buffer for Partition-wise GpuJoin
- GPU-aware pinned-inner-buffer HOT 1
- [JP] nvidia-gdsもバージョンを指定するべき HOT 2
- PG17beta2でインストールエラー HOT 1
- PG17beta2: CREATE EXTENSION pg_strom;でエラー HOT 2
- [idea] SUM(numeric) での浮動小数点誤差
- [VTJ-JP]CUDAバージョンに関する説明 HOT 3
- pg2arrowのdecimal型変換がめっちゃ重い HOT 1
- [JP][docs-issue]RHEL8における設定に翻訳漏れとコマンド記述ミスがある
- [JP][docs-issue] GPUキャッシュドキュメントにPostgreSQL 15以前のバージョン固有の情報がある
- Too much GPU workers startup/terminate if pg_strom.max_async_tasks in postgresql.conf
- [VTJ-JP]Crashed in cpu fallback process(?)(in using ST_Crosses function)
- SELECT DISTINCTの行推定
- PG17: SELECT distinct のEXPLAINのnrowsが実行のたびに変動する
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pg-strom.