postgresql - postgres查询优化

Question

我正在为 postgres 上的以下查询寻找优化建议。不是 DBA，所以在这里寻找一些专家建议。

设备表包含十六进制的 device_id。为了实现高吞吐量，我们并行运行此查询的 6 个实例，并对 device_id 以 [0-2]、[3-5]、[6-9]、[ac]、[df] 开头的模式匹配

当我们只运行一个查询实例时，它工作正常，但有 6 个实例我们得到错误 - [6669]:FATAL: connection to client lost

explain analyze select notifications.id, notifications.status, events.alert_type,
  events.id as event_id, events.payload, notifications.device_id as device_id,
  device_endpoints.region, device_endpoints.device_endpoint as endpoint
  from notifications
  inner join events
  on notifications.event_id = events.id
  inner join devices
  on notifications.device_id = devices.id
  inner join device_endpoints   
  on devices.id = device_endpoints.device_id
  where notifications.status = 'pending' AND notifications.region = 'ap-southeast-2' 
  AND devices.device_id ~ '[0-9a-f].*'
  limit 10000; 

解释分析的输出

"Limit  (cost=25.62..1349.23 rows=206 width=202) (actual time=0.359..0.359 rows=0 loops=1)"
"  ->  Nested Loop  (cost=25.62..1349.23 rows=206 width=202) (actual time=0.357..0.357 rows=0 loops=1)"
"        Join Filter: (notifications.device_id = devices.id)"
"        ->  Nested Loop  (cost=25.33..1258.73 rows=206 width=206) (actual time=0.357..0.357 rows=0 loops=1)"
"              ->  Hash Join  (cost=25.04..61.32 rows=206 width=52) (actual time=0.043..0.172 rows=193 loops=1)"
"                    Hash Cond: (notifications.event_id = events.id)"
"                    ->  Index Scan using idx_notifications_status on notifications  (cost=0.42..33.87 rows=206 width=16) (actual time=0.013..0.100 rows=193 loops=1)"
"                          Index Cond: (status = 'pending'::notification_status)"
"                          Filter: (region = 'ap-southeast-2'::text)"
"                    ->  Hash  (cost=16.50..16.50 rows=650 width=40) (actual time=0.022..0.022 rows=34 loops=1)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 14kB"
"                          ->  Seq Scan on events  (cost=0.00..16.50 rows=650 width=40) (actual time=0.005..0.014 rows=34 loops=1)"
"              ->  Index Scan using idx_device_endpoints_device_id on device_endpoints  (cost=0.29..5.80 rows=1 width=154) (actual time=0.001..0.001 rows=0 loops=193)"
"                    Index Cond: (device_id = notifications.device_id)"
"        ->  Index Scan using devices_pkey on devices  (cost=0.29..0.43 rows=1 width=4) (never executed)"
"              Index Cond: (id = device_endpoints.device_id)"
"              Filter: (device_id ~ '[0-9a-f].*'::text)"
"Planning time: 0.693 ms"
"Execution time: 0.404 ms"

标签： postgresql