Closed kaigai closed 16 hours ago
修正done
tpch=# explain analyze
select sum(l_extendedprice* (1 - l_discount)) as revenue
from lineitem,
part
where (p_partkey = l_partkey
and p_brand = 'Brand#13'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 7 and l_quantity <= 7 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON')
or (p_partkey = l_partkey
and p_brand = 'Brand#15'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 18 and l_quantity <= 18 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON')
or (p_partkey = l_partkey
and p_brand = 'Brand#35'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 21 and l_quantity <= 21 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=13900844.78..13900844.79 rows=1 width=32) (actual time=43551.077..43554.170 rows=1 loops=1)
-> Gather (cost=13900844.67..13900844.78 rows=1 width=32) (actual time=43550.062..43554.150 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Custom Scan (GpuPreAgg) on lineitem (cost=13899844.67..13899844.68 rows=1 width=32) (actual time=43541.941..43541.944 rows=0 loops=3)
GPU Projection: pgstrom.psum(((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))::double precision)
GPU Scan Quals: ((lineitem.l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (lineitem.l_shipinstruct = 'DELIVER IN PERSON'::bpchar) AND (((lineitem.l_quantity >= '7'::numeric) AND (lineitem.l_quantity <= '17'::numeric)) OR ((lineitem.l_quantity >= '18'::numeric) AND (lineitem.l_quantity <= '28'::numeric)) OR ((lineitem.l_quantity >= '21'::numeric) AND (lineitem.l_quantity <= '31'::numeric)))) [plan: 6000244000 -> 46040140, exec: 5999989709 -> 107136221]
GPU Join Quals [1]: (((part.p_brand = 'Brand#13'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '7'::numeric) AND (lineitem.l_quantity <= '17'::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#15'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (lineitem.l_quantity >= '18'::numeric) AND (lineitem.l_quantity <= '28'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#35'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '21'::numeric) AND (lineitem.l_quantity <= '31'::numeric) AND (part.p_size <= 15))), (lineitem.l_partkey = part.p_partkey) ... [plan: 46040140 -> 21919, exec: 107136221 -> 113970]
GPU Outer Hash [1]: lineitem.l_partkey
GPU Inner Hash [1]: part.p_partkey
GPU-Direct SQL: enabled (GPU-0; direct=115517748, ntuples=5999989709)
-> Parallel Custom Scan (GpuScan) on part (cost=100.00..499289.45 rows=197992 width=30) (actual time=28.694..1650.528 rows=159661 loops=3)
GPU Projection: p_partkey, p_brand, p_container, p_size
GPU Scan Quals: ((p_size >= 1) AND (((p_brand = 'Brand#13'::bpchar) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (p_size <= 5)) OR ((p_brand = 'Brand#15'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10)) OR ((p_brand = 'Brand#35'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (p_size <= 15)))) [plan: 200013300 -> 197992, exec: 200000000 -> 478984]
GPU-Direct SQL: enabled (GPU-0; direct=4096015, ntuples=200000000)
Planning Time: 3.858 ms
Execution Time: 43555.392 ms
(17 rows)
条件句がクッソ長いんだよなぁ。。。