OS: Rocky Linux 9.2
CockroachDB: v23.2.0
cockroach sql \
--certs-dir=/cockroach/crc/certs \
--host=mmm191
-- 1. テストデータ作成
cockroach workload init movr postgresql://myuser:myuser@mmm191:26257?sslmode=require&sslcert=/cockroach/crc/certs/client.myuser.crt&sslkey=/cockroach/crc/certs/client.myuser.key&sslrootcert=/cockroach/crc/certs/ca.crt
cockroach workload run movr --duration 10s postgresql://myuser:myuser@mmm191:26257?sslmode=require&sslcert=/cockroach/crc/certs/client.myuser.crt&sslkey=/cockroach/crc/certs/client.myuser.key&sslrootcert=/cockroach/crc/certs/ca.crt \
-- 2. 実行計画
show statements;
explain
select * from rides where end_address = '77215 Gina Lake Apt. 13';
explain analyze
select * from rides where end_address = '77215 Gina Lake Apt. 13';
-- 3. ヒント句
-- 3.1 インデックスヒント
show create table rides;
explain analyze
select start_time, end_time
from rides
where city = 'amsterdam'
and end_address = '7215 Gina Lake Apt. 13'
;
explain analyze
select start_time, end_time
from rides@rides_auto_index_fk_city_ref_users
where city = 'amsterdam'
and end_address = '7215 Gina Lake Apt. 13'
;
-- 3.2 結合ヒント
explain analyze
select count(*) from rides r
inner join vehicles v
on r.vehicle_city = v.city
and r.vehicle_id = v.id
;
explain analyze
select count(*) from rides r
inner merge join vehicles v
on r.vehicle_city = v.city
and r.vehicle_id = v.id
;
explain analyze
select count(*) from rides r
inner lookup join vehicles v
on r.vehicle_city = v.city
and r.vehicle_id = v.id
;
explain analyze
select count(*) from rides r
inner hash join vehicles v
on r.vehicle_city = v.city
and r.vehicle_id = v.id
;
-- 4. 統計情報
show statistics for table rides;
create statistics stats01 from rides;
