はじめに
2025年9月にPostgreSQL 18がリリースされました。
uuid v7がネイティブサポートされてうれしい！という話をチラホラ聞いたりもしますが、個人的にはasyncの導入が激アツではないかと思っています。
本記事では、GISクラスタの端くれとして、PostGISを題材に、18のasyncでどれくらいパフォーマンス差が出るのか検証してみたいと思います。
実行環境
手元でやるより条件を揃えやすいのでクラウド環境を利用します。筆者はAWSのほか、SupabaseやNeonを使いますが、執筆時点でAWSのAmazon RDSにのみ18が降ってきているので、AWSを利用します。設定はデフォルトで以下です。
- インスタンスタイプ：db.m7g.large
- ストレージ：gp3
asyncを有効化するためにパラメータグループをいじる
paramter groupを作成し
io_methodをworkerに。なおio_uringはサポート外
RDSにアタッチ
アタッチしてないRDSと2台用意して、それぞれに同一のクエリを投げて実行計画を眺めます。
データとクエリ
- たまたま手元にあるOvertureMapsのbuildings（北海道エリア、300万ポリゴン）を投入する
- このテーブルからMVTを配信するWebAPIを実装する
- タイルリクエストをしてようすを見る
すごく厳密なベンチマークをとるつもりはなく、傾向を掴みたい所存です（言い訳）
データ投入
ogr2ogr -f PostgreSQL postgresql://username:password@pg17.<suppressed>.ap-northeast-1.rds.amazonaws.com:5432/postgres buildings.parquet -progress
クエリ
ST_AsMVTでMVTを作るクエリ
// drizzleを使ってサーバーを書いたのでJavaScriptの文法
const _sql = sql`
WITH bounds AS (
SELECT ST_TileEnvelope(${z}::int, ${x}::int, ${y}::int) AS geom
),
mvtgeom AS (
SELECT
ST_AsMVTGeom(
ST_Transform(buildings.geometry, 3857),
bounds.geom,
4096,
256,
true
) AS geom,
buildings.ogc_fid
FROM public.buildings, bounds
WHERE
buildings.geometry && ST_Transform(bounds.geom, 4326)
AND ST_Intersects(buildings.geometry, ST_Transform(bounds.geom, 4326))
)
SELECT ST_AsMVT(mvtgeom.*, 'layer', 4096, 'geom') FROM mvtgeom;
`;
実行計画を眺める。以下は同一のクエリを実行した際のpg18sync/asyncの実行計画。
実行計画（ヒットするレコード多め）
{
pg18Sync: [
'Aggregate (cost=2850.42..2850.43 rows=1 width=32) (actual time=2908.535..2908.536 rows=1.00 loops=1)',
' Buffers: shared hit=1913',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..1557.29 rows=94 width=146) (actual time=0.159..528.852 rows=20564.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry)",
' Rows Removed by Filter: 3',
' Index Searches: 1',
' Buffers: shared hit=1907',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 57.249 ms',
'Execution Time: 2908.974 ms'
],
pg18Async: [
'Aggregate (cost=4212.57..4212.58 rows=1 width=32) (actual time=382.600..382.601 rows=1.00 loops=1)',
' Buffers: shared hit=1940',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..2300.69 rows=139 width=146) (actual time=0.166..39.303 rows=20564.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry)",
' Rows Removed by Filter: 3',
' Index Searches: 1',
' Buffers: shared hit=1934',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 14.621 ms',
'Execution Time: 383.089 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=0.017..0.018 rows=1.00 loops=1)',
' Buffers: shared hit=1',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.015..0.015 rows=0.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry)",
' Index Searches: 1',
' Buffers: shared hit=1',
'Planning Time: 0.228 ms',
'Execution Time: 0.044 ms'
],
pg18Async: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=0.015..0.016 rows=1.00 loops=1)',
' Buffers: shared hit=1',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.013..0.013 rows=0.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry)",
' Index Searches: 1',
' Buffers: shared hit=1',
'Planning Time: 0.238 ms',
'Execution Time: 0.042 ms'
]
}
{
pg18Sync: [
'Finalize Aggregate (cost=38261.36..38261.37 rows=1 width=32) (actual time=4998.542..4998.785 rows=1.00 loops=1)',
' Buffers: shared hit=4714',
' -> Gather (cost=38258.03..38258.24 rows=2 width=32) (actual time=4869.411..4992.054 rows=2.00 loops=1)',
' Workers Planned: 1',
' Workers Launched: 1',
' Buffers: shared hit=4714',
' -> Partial Aggregate (cost=37258.03..37258.04 rows=1 width=32) (actual time=4845.381..4845.383 rows=1.00 loops=2)',
' Buffers: shared hit=4714',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=68.38..21499.90 rows=1146 width=146) (actual time=22.819..520.160 rows=37820.50 loops=2)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry)",
' Rows Removed by Filter: 2',
' Heap Blocks: exact=1936',
' Buffers: shared hit=4313',
' Worker 0: Heap Blocks: exact=1882',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..67.89 rows=1948 width=0) (actual time=45.070..45.071 rows=75646.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=475',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 42.964 ms',
'Execution Time: 5012.801 ms'
],
pg18Async: [
'Finalize Aggregate (cost=38679.15..38679.16 rows=1 width=32) (actual time=646.313..646.568 rows=1.00 loops=1)',
' Buffers: shared hit=4711',
' -> Gather (cost=38675.81..38676.02 rows=2 width=32) (actual time=640.258..640.628 rows=2.00 loops=1)',
' Workers Planned: 1',
' Workers Launched: 1',
' Buffers: shared hit=4711',
' -> Partial Aggregate (cost=37675.81..37675.82 rows=1 width=32) (actual time=635.010..635.011 rows=1.00 loops=2)',
' Buffers: shared hit=4711',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=68.61..21738.94 rows=1159 width=146) (actual time=3.306..63.444 rows=37820.50 loops=2)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry)",
' Rows Removed by Filter: 2',
' Heap Blocks: exact=1924',
' Buffers: shared hit=4315',
' Worker 0: Heap Blocks: exact=1894',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..68.11 rows=1970 width=0) (actual time=6.069..6.069 rows=75646.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161408C938850D18845400000000000C16140D8DD5F3F4ACA45400000000080D76140D8DD5F3F4ACA45400000000080D761408C938850D18845400000000000C161408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=477',
'Planning Time: 0.228 ms',
'Execution Time: 647.152 ms'
]
}
{
pg18Sync: [
'Finalize Aggregate (cost=22863.26..22863.27 rows=1 width=32) (actual time=7425.203..7425.420 rows=1.00 loops=1)',
' Buffers: shared hit=3035',
' -> Gather (cost=22859.93..22860.14 rows=2 width=32) (actual time=7416.157..7416.376 rows=1.00 loops=1)',
' Workers Planned: 1',
' Workers Launched: 0',
' Buffers: shared hit=3035',
' -> Partial Aggregate (cost=21859.93..21859.94 rows=1 width=32) (actual time=7415.698..7415.700 rows=1.00 loops=1)',
' Buffers: shared hit=3035',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=40.08..12660.55 rows=669 width=146) (actual time=54.629..750.499 rows=52744.00 loops=1)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry)",
' Rows Removed by Filter: 10',
' Heap Blocks: exact=2694',
' Buffers: shared hit=3029',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..39.79 rows=1138 width=0) (actual time=54.308..54.308 rows=52754.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=335',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 62.722 ms',
'Execution Time: 7425.640 ms'
],
pg18Async: [
'Aggregate (cost=28942.26..28942.27 rows=1 width=32) (actual time=952.325..952.327 rows=1.00 loops=1)',
' Buffers: shared hit=3029',
' -> Bitmap Heap Scan on bq_rg f (cost=34.28..15700.38 rows=963 width=146) (actual time=4.205..90.157 rows=52744.00 loops=1)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry)",
' Rows Removed by Filter: 10',
' Heap Blocks: exact=2694',
' Buffers: shared hit=3029',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..34.04 rows=963 width=0) (actual time=3.918..3.919 rows=52754.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000080AA6140D8DD5F3F4ACA45400000000080AA614053F777FC350B46400000000000C1614053F777FC350B46400000000000C16140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA4540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=335',
'Planning Time: 0.221 ms',
'Execution Time: 952.434 ms'
]
}
{
pg18Sync: [
'Finalize Aggregate (cost=52168.99..52169.00 rows=1 width=32) (actual time=5904.307..5904.568 rows=1.00 loops=1)',
' Buffers: shared hit=5572',
' -> Gather (cost=52165.65..52165.86 rows=2 width=32) (actual time=5824.516..5827.872 rows=2.00 loops=1)',
' Workers Planned: 1',
' Workers Launched: 1',
' Buffers: shared hit=5572',
' -> Partial Aggregate (cost=51165.65..51165.66 rows=1 width=32) (actual time=5804.421..5804.422 rows=1.00 loops=2)',
' Buffers: shared hit=5572',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=91.93..29453.78 rows=1579 width=146) (actual time=18.955..558.732 rows=43678.50 loops=2)',
" Recheck Cond: (geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry)",
' Rows Removed by Filter: 16',
' Heap Blocks: exact=2369',
' Buffers: shared hit=5177',
' Worker 0: Heap Blocks: exact=2219',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..91.26 rows=2685 width=0) (actual time=37.283..37.283 rows=87388.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=569',
'Planning Time: 0.228 ms',
'Execution Time: 5915.466 ms'
],
pg18Async: [
'Finalize Aggregate (cost=55040.95..55040.96 rows=1 width=32) (actual time=828.087..828.335 rows=1.00 loops=1)',
' Buffers: shared hit=5570',
' -> Gather (cost=55037.61..55037.82 rows=2 width=32) (actual time=819.079..821.094 rows=2.00 loops=1)',
' Workers Planned: 1',
' Workers Launched: 1',
' Buffers: shared hit=5570',
' -> Partial Aggregate (cost=54037.61..54037.62 rows=1 width=32) (actual time=814.374..814.376 rows=1.00 loops=2)',
' Buffers: shared hit=5570',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=97.49..31088.24 rows=1669 width=146) (actual time=3.891..76.857 rows=43678.50 loops=2)',
" Recheck Cond: (geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry)",
' Rows Removed by Filter: 16',
' Heap Blocks: exact=2318',
' Buffers: shared hit=5174',
' Worker 0: Heap Blocks: exact=2270',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..96.78 rows=2837 width=0) (actual time=7.142..7.143 rows=87388.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000009461404F90BC55CB46454000000000009461408C938850D18845400000000080AA61408C938850D18845400000000080AA61404F90BC55CB46454000000000009461404F90BC55CB464540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=566',
'Planning Time: 0.229 ms',
'Execution Time: 828.388 ms'
]
}
{
pg18Sync: [
'Finalize Aggregate (cost=692094.80..692094.81 rows=1 width=32) (actual time=14348.370..14349.210 rows=1.00 loops=1)',
' Buffers: shared hit=23264',
' -> Gather (cost=692091.47..692091.68 rows=2 width=32) (actual time=14210.823..14217.167 rows=3.00 loops=1)',
' Workers Planned: 2',
' Workers Launched: 2',
' Buffers: shared hit=23264',
' -> Partial Aggregate (cost=691091.47..691091.48 rows=1 width=32) (actual time=14171.284..14171.286 rows=1.00 loops=3)',
' Buffers: shared hit=23264',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=1785.23..386569.59 rows=22147 width=146) (actual time=215.407..1744.099 rows=130623.33 loops=3)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry)",
' Rows Removed by Filter: 18',
' Heap Blocks: exact=6700',
' Buffers: shared hit=22468',
' Worker 0: Heap Blocks: exact=6669',
' Worker 1: Heap Blocks: exact=6685',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..1771.94 rows=53153 width=0) (actual time=177.372..177.373 rows=391924.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=2374',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 53.804 ms',
'Execution Time: 14353.303 ms'
],
pg18Async: [
'Finalize Aggregate (cost=667920.51..667920.52 rows=1 width=32) (actual time=5944.363..5982.245 rows=1.00 loops=1)',
' Buffers: shared hit=23227',
' -> Gather (cost=667917.17..667917.38 rows=2 width=32) (actual time=5858.280..5936.640 rows=3.00 loops=1)',
' Workers Planned: 2',
' Workers Launched: 2',
' Buffers: shared hit=23227',
' -> Partial Aggregate (cost=666917.17..666917.18 rows=1 width=32) (actual time=5845.540..5845.541 rows=1.00 loops=3)',
' Buffers: shared hit=23227',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=1720.80..373807.80 rows=21317 width=146) (actual time=25.002..698.918 rows=130623.33 loops=3)',
" Recheck Cond: (geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry)",
' Rows Removed by Filter: 18',
' Heap Blocks: exact=6973',
' Buffers: shared hit=22435',
' Worker 0: Heap Blocks: exact=6828',
' Worker 1: Heap Blocks: exact=6253',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..1708.01 rows=51160 width=0) (actual time=30.688..30.689 rows=391924.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000080AA61408C938850D18845400000000080AA6140D8DD5F3F4ACA45400000000000C16140D8DD5F3F4ACA45400000000000C161408C938850D18845400000000080AA61408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=2341',
'Planning Time: 0.238 ms',
'Execution Time: 5994.457 ms'
]
}
{
pg18Sync: [
'Finalize Aggregate (cost=212092.10..212092.11 rows=1 width=32) (actual time=13811.759..13812.260 rows=1.00 loops=1)',
' Buffers: shared hit=12514',
' -> Gather (cost=212088.77..212088.98 rows=2 width=32) (actual time=13798.179..13798.680 rows=1.00 loops=1)',
' Workers Planned: 2',
' Workers Launched: 0',
' Buffers: shared hit=12514',
' -> Partial Aggregate (cost=211088.77..211088.78 rows=1 width=32) (actual time=13797.705..13797.707 rows=1.00 loops=1)',
' Buffers: shared hit=12514',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=511.92..124174.39 rows=6321 width=146) (actual time=118.747..1410.100 rows=214367.00 loops=1)',
" Recheck Cond: (geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry)",
' Rows Removed by Filter: 29',
' Heap Blocks: exact=11204',
' Buffers: shared hit=12514',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..508.12 rows=15171 width=0) (actual time=117.529..117.529 rows=214396.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=1310',
'Planning Time: 0.217 ms',
'Execution Time: 13813.703 ms'
],
pg18Async: [
'Finalize Aggregate (cost=219159.77..219159.78 rows=1 width=32) (actual time=6166.619..6167.110 rows=1.00 loops=1)',
' Buffers: shared hit=13348',
' -> Gather (cost=219156.44..219156.65 rows=2 width=32) (actual time=6114.748..6152.140 rows=3.00 loops=1)',
' Workers Planned: 2',
' Workers Launched: 2',
' Buffers: shared hit=13348',
' -> Partial Aggregate (cost=218156.44..218156.45 rows=1 width=32) (actual time=6090.249..6090.251 rows=1.00 loops=3)',
' Buffers: shared hit=13348',
' -> Parallel Bitmap Heap Scan on bq_rg f (cost=529.34..128203.31 rows=6542 width=146) (actual time=48.576..609.143 rows=71455.67 loops=3)',
" Recheck Cond: (geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry)",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry)",
' Rows Removed by Filter: 10',
' Heap Blocks: exact=3781',
' Buffers: shared hit=12550',
' Worker 0: Heap Blocks: exact=3680',
' Worker 1: Heap Blocks: exact=3743',
' -> Bitmap Index Scan on bq_rg_geometry_geom_idx (cost=0.00..525.41 rows=15700 width=0) (actual time=54.783..54.783 rows=214396.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000009461408C938850D18845400000000000946140D8DD5F3F4ACA45400000000080AA6140D8DD5F3F4ACA45400000000080AA61408C938850D188454000000000009461408C938850D1884540'::geometry))",
' Index Searches: 1',
' Buffers: shared hit=1306',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 51.461 ms',
'Execution Time: 6168.499 ms'
]
}
実行計画（ヒットするレコード少なめ）
{
pg18Sync: [
'Aggregate (cost=216.93..216.94 rows=1 width=32) (actual time=424.191..424.192 rows=1.00 loops=1)',
' Buffers: shared hit=341',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..120.05 rows=7 width=146) (actual time=0.133..6.610 rows=2663.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=335',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 34.613 ms',
'Execution Time: 424.417 ms'
],
pg18Async: [
'Aggregate (cost=186.66..186.67 rows=1 width=32) (actual time=105.514..105.515 rows=1.00 loops=1)',
' Buffers: shared hit=325',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..103.53 rows=6 width=146) (actual time=0.141..6.406 rows=2663.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=319',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 14.389 ms',
'Execution Time: 105.727 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=380.362..380.363 rows=1.00 loops=1)',
' Buffers: shared hit=257',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.134..15.859 rows=2315.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 1',
' Index Searches: 1',
' Buffers: shared hit=251',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 52.362 ms',
'Execution Time: 380.598 ms'
],
pg18Async: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=130.899..130.900 rows=1.00 loops=1)',
' Buffers: shared hit=254',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.125..5.462 rows=2315.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 1',
' Index Searches: 1',
' Buffers: shared hit=248',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 23.726 ms',
'Execution Time: 131.086 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=95.85..95.86 rows=1 width=32) (actual time=431.336..431.337 rows=1.00 loops=1)',
' Buffers: shared hit=336',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..53.97 rows=3 width=146) (actual time=0.130..7.223 rows=2919.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 4',
' Index Searches: 1',
' Buffers: shared hit=330',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 53.371 ms',
'Execution Time: 431.572 ms'
],
pg18Async: [
'Aggregate (cost=186.66..186.67 rows=1 width=32) (actual time=164.077..164.078 rows=1.00 loops=1)',
' Buffers: shared hit=355',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..103.53 rows=6 width=146) (actual time=0.129..11.601 rows=2919.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry)",
' Rows Removed by Filter: 4',
' Index Searches: 1',
' Buffers: shared hit=349',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 23.486 ms',
'Execution Time: 164.316 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=93.973..93.974 rows=1.00 loops=1)',
' Buffers: shared hit=108',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.044..32.581 rows=1110.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry)",
' Index Searches: 1',
' Buffers: shared hit=108',
'Planning Time: 0.247 ms',
'Execution Time: 94.014 ms'
],
pg18Async: [
'Aggregate (cost=35.31..35.32 rows=1 width=32) (actual time=81.956..81.956 rows=1.00 loops=1)',
' Buffers: shared hit=109',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..20.93 rows=1 width=146) (actual time=0.048..12.595 rows=1110.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry)",
' Index Searches: 1',
' Buffers: shared hit=109',
'Planning Time: 0.223 ms',
'Execution Time: 81.995 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=277.47..277.48 rows=1 width=32) (actual time=670.448..670.448 rows=1.00 loops=1)',
' Buffers: shared hit=644',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..153.09 rows=9 width=146) (actual time=0.137..14.976 rows=5827.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=638',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 53.568 ms',
'Execution Time: 670.883 ms'
],
pg18Async: [
'Aggregate (cost=65.58..65.59 rows=1 width=32) (actual time=251.520..251.520 rows=1.00 loops=1)',
' Buffers: shared hit=631',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..37.45 rows=2 width=146) (actual time=0.135..13.103 rows=5827.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB614093170BC73C9945400000000060BB6140D5C3C9336FA145400000000030BE6140D5C3C9336FA145400000000030BE614093170BC73C9945400000000060BB614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=625',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 13.947 ms',
'Execution Time: 251.941 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=95.85..95.86 rows=1 width=32) (actual time=161.532..161.533 rows=1.00 loops=1)',
' Buffers: shared hit=222',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..53.97 rows=3 width=146) (actual time=0.059..15.745 rows=2451.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=222',
'Planning Time: 0.222 ms',
'Execution Time: 161.572 ms'
],
pg18Async: [
'Aggregate (cost=65.58..65.59 rows=1 width=32) (actual time=184.242..184.243 rows=1.00 loops=1)',
' Buffers: shared hit=220',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..37.45 rows=2 width=146) (actual time=0.069..5.750 rows=2451.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140BB7A166C9FA9454000000000C0B56140AAFCDD6FCDB145400000000090B86140AAFCDD6FCDB145400000000090B86140BB7A166C9FA9454000000000C0B56140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 2',
' Index Searches: 1',
' Buffers: shared hit=220',
'Planning Time: 0.205 ms',
'Execution Time: 184.280 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=1306.65..1306.66 rows=1 width=32) (actual time=673.684..673.685 rows=1.00 loops=1)',
' Buffers: shared hit=1213',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..714.77 rows=43 width=146) (actual time=0.147..47.778 rows=10408.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 7',
' Index Searches: 1',
' Buffers: shared hit=1207',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 43.769 ms',
'Execution Time: 674.329 ms'
],
pg18Async: [
'Aggregate (cost=580.17..580.18 rows=1 width=32) (actual time=594.076..594.077 rows=1.00 loops=1)',
' Buffers: shared hit=1191',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..318.29 rows=19 width=146) (actual time=0.141..24.027 rows=10408.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B5614093170BC73C99454000000000C0B56140D5C3C9336FA145400000000090B86140D5C3C9336FA145400000000090B8614093170BC73C99454000000000C0B5614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 7',
' Index Searches: 1',
' Buffers: shared hit=1185',
'Planning:',
' Buffers: shared hit=285',
'Planning Time: 14.432 ms',
'Execution Time: 594.699 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=943.41..943.42 rows=1 width=32) (actual time=363.937..363.938 rows=1.00 loops=1)',
' Buffers: shared hit=724',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..516.53 rows=31 width=146) (actual time=0.064..35.861 rows=6748.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry)",
' Rows Removed by Filter: 8',
' Index Searches: 1',
' Buffers: shared hit=724',
'Planning Time: 0.230 ms',
'Execution Time: 363.978 ms'
],
pg18Async: [
'Aggregate (cost=1609.35..1609.36 rows=1 width=32) (actual time=471.457..471.458 rows=1.00 loops=1)',
' Buffers: shared hit=717',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..879.97 rows=53 width=146) (actual time=0.059..25.782 rows=6748.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B861407873EE25089145400000000090B8614093170BC73C9945400000000060BB614093170BC73C9945400000000060BB61407873EE25089145400000000090B861407873EE2508914540'::geometry)",
' Rows Removed by Filter: 8',
' Index Searches: 1',
' Buffers: shared hit=717',
'Planning Time: 0.217 ms',
'Execution Time: 471.747 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=186.66..186.67 rows=1 width=32) (actual time=163.058..163.059 rows=1.00 loops=1)',
' Buffers: shared hit=440',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..103.53 rows=6 width=146) (actual time=0.057..9.256 rows=3985.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 6',
' Index Searches: 1',
' Buffers: shared hit=440',
'Planning Time: 0.204 ms',
'Execution Time: 163.138 ms'
],
pg18Async: [
'Aggregate (cost=247.20..247.21 rows=1 width=32) (actual time=313.765..313.765 rows=1.00 loops=1)',
' Buffers: shared hit=435',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..136.57 rows=8 width=146) (actual time=0.054..9.367 rows=3985.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140BB7A166C9FA945400000000090B86140AAFCDD6FCDB145400000000060BB6140AAFCDD6FCDB145400000000060BB6140BB7A166C9FA945400000000090B86140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 6',
' Index Searches: 1',
' Buffers: shared hit=435',
'Planning Time: 0.228 ms',
'Execution Time: 313.851 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=1336.92..1336.93 rows=1 width=32) (actual time=1134.517..1134.517 rows=1.00 loops=1)',
' Buffers: shared hit=1649',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..731.29 rows=44 width=146) (actual time=0.153..52.181 rows=14336.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 8',
' Index Searches: 1',
' Buffers: shared hit=1643',
'Planning:',
' Buffers: shared hit=284',
'Planning Time: 13.928 ms',
'Execution Time: 1135.281 ms'
],
pg18Async: [
'Aggregate (cost=1942.32..1942.33 rows=1 width=32) (actual time=874.844..874.845 rows=1.00 loops=1)',
' Buffers: shared hit=1576',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..1061.69 rows=64 width=146) (actual time=0.054..79.021 rows=14336.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B8614093170BC73C9945400000000090B86140D5C3C9336FA145400000000060BB6140D5C3C9336FA145400000000060BB614093170BC73C9945400000000090B8614093170BC73C994540'::geometry)",
' Rows Removed by Filter: 8',
' Index Searches: 1',
' Buffers: shared hit=1576',
'Planning Time: 0.212 ms',
'Execution Time: 875.291 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=338.01..338.02 rows=1 width=32) (actual time=264.465..264.465 rows=1.00 loops=1)',
' Buffers: shared hit=802',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..186.13 rows=11 width=146) (actual time=0.053..15.979 rows=6926.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 5',
' Index Searches: 1',
' Buffers: shared hit=802',
'Planning Time: 0.207 ms',
'Execution Time: 264.769 ms'
],
pg18Async: [
'Aggregate (cost=247.20..247.21 rows=1 width=32) (actual time=335.949..335.950 rows=1.00 loops=1)',
' Buffers: shared hit=787',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..136.57 rows=8 width=146) (actual time=0.063..26.248 rows=6926.00 loops=1)',
" Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140BB7A166C9FA945400000000060BB6140AAFCDD6FCDB145400000000030BE6140AAFCDD6FCDB145400000000030BE6140BB7A166C9FA945400000000060BB6140BB7A166C9FA94540'::geometry)",
' Rows Removed by Filter: 5',
' Index Searches: 1',
' Buffers: shared hit=787',
'Planning Time: 0.206 ms',
'Execution Time: 336.248 ms'
]
}
{
pg18Sync: [
'Aggregate (cost=2880.69..2880.70 rows=1 width=32) (actual time=737.395..737.396 rows=1.00 loops=1)',
' Buffers: shared hit=1923',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..1573.81 rows=95 width=146) (actual time=0.075..39.781 rows=17451.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry)",
' Rows Removed by Filter: 12',
' Index Searches: 1',
' Buffers: shared hit=1923',
'Planning Time: 0.256 ms',
'Execution Time: 737.884 ms'
],
pg18Async: [
'Aggregate (cost=2123.94..2123.95 rows=1 width=32) (actual time=725.440..725.441 rows=1.00 loops=1)',
' Buffers: shared hit=1957',
' -> Index Scan using bq_rg_geometry_geom_idx on bq_rg f (cost=0.41..1160.81 rows=70 width=146) (actual time=0.075..40.307 rows=17451.00 loops=1)',
" Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry))",
" Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B561407873EE250891454000000000C0B5614093170BC73C9945400000000090B8614093170BC73C9945400000000090B861407873EE250891454000000000C0B561407873EE2508914540'::geometry)",
' Rows Removed by Filter: 12',
' Index Searches: 1',
' Buffers: shared hit=1957',
'Planning Time: 0.219 ms',
'Execution Time: 725.685 ms'
]
}
検証結果
うーん、よくわからん。ただし大体同じ実行計画でありながらasyncの方が実行時間が短いケース多いので、恩恵があることにしたいです。ここから何かを読み取れる有識者がおられたらコメントいただきたいところです。
体感的にはある程度多くのレコードがヒットするクエリの場合に差がつきやすい感じです。PostGISだとそういうケースが多いので、asyncを利用するとパフォーマンスに効いてくるかもしれません。