sql—进一步优化~20m记录表中“inet”列的“位图索引扫描”的可能性

jfewjypa 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(397)

我正在优化一个简单表上的查询，这个表有大约21m条记录。表的两个主要列是 node_ip_addr 以及 nbr_ip_addr ，都是 inet ，存储邻居信息数据。因此，如果a是b的邻居，我们可以在表中有以下两个条目：

A -> B
B -> A

下表为ddl：

CREATE TABLE tbl_relation (
    id serial NOT NULL,
    node_ip_addr inet NULL,
    nbr_ip_addr inet NULL,
);

表中的索引：

idx_tbl_relation_id                     CREATE INDEX idx_tbl_relation_id ON tbl_relation USING btree (id)
idx_tbl_relation_node_ip_addr_gist      CREATE INDEX idx_tbl_relation_node_ip_addr_gist ON tbl_relation USING gist (node_ip_addr inet_ops)
idx_tbl_relation_nbr_ip_addr_gist       CREATE INDEX idx_tbl_relation_nbr_ip_addr_gist ON tbl_relation USING gist (nbr_ip_addr inet_ops)

注意：我们已经在table上试过吸尘器了：

vacuum analyze tbl_relation;

下面是要优化的查询：

explain (analyze,buffers) SELECT * FROM  tbl_relation WHERE (node_ip_addr = '10.14.221.167' OR nbr_ip_addr = '10.14.221.167') AND (node_ip_addr = '10.14.9.185'   OR nbr_ip_addr = '10.14.9.185');

Bitmap Heap Scan on tbl_relation  (cost=459.24..463.26 rows=1 width=71) (actual time=142.336..142.336 rows=0 loops=1)
      Recheck Cond: (((node_ip_addr = '10.14.221.167'::inet) OR (nbr_ip_addr = '10.14.221.167'::inet)) AND ((node_ip_addr = '10.14.9.185'::inet) OR (nbr_ip_addr = '10.14.9.185'::inet)))
      Buffers: shared hit=13789
      ->  BitmapAnd  (cost=459.24..459.24 rows=1 width=0) (actual time=142.332..142.332 rows=0 loops=1)
            Buffers: shared hit=13789
            ->  BitmapOr  (cost=33.05..33.05 rows=1095 width=0) (actual time=70.667..70.667 rows=0 loops=1)
                  Buffers: shared hit=6894
                  ->  Bitmap Index Scan on idx_tbl_relation_node_ip_addr_gist  (cost=0.00..11.30 rows=385 width=0) (actual time=44.895..44.895 rows=10 loops=1)
                        Index Cond: (node_ip_addr = '10.14.221.167'::inet)
                        Buffers: shared hit=4256
                  ->  Bitmap Index Scan on idx_tbl_relation_nbr_ip_addr_gist  (cost=0.00..21.74 rows=710 width=0) (actual time=25.767..25.767 rows=3 loops=1)
                        Index Cond: (nbr_ip_addr = '10.14.221.167'::inet)
                        Buffers: shared hit=2638
            ->  BitmapOr  (cost=425.94..425.94 rows=16147 width=0) (actual time=71.651..71.651 rows=0 loops=1)
                  Buffers: shared hit=6895
                  ->  Bitmap Index Scan on idx_tbl_relation_node_ip_addr_gist  (cost=0.00..404.19 rows=15437 width=0) (actual time=45.983..45.983 rows=15831 loops=1)
                        Index Cond: (node_ip_addr = '10.14.9.185'::inet)
                        Buffers: shared hit=4262
                  ->  Bitmap Index Scan on idx_tbl_relation_nbr_ip_addr_gist  (cost=0.00..21.74 rows=710 width=0) (actual time=25.662..25.662 rows=0 loops=1)
                        Index Cond: (nbr_ip_addr = '10.14.9.185'::inet)
                        Buffers: shared hit=2633
    Planning Time: 0.159 ms
    Execution Time: 142.461 ms

有关数据的一些信息：

select count(*) from tbl_relation;
-- 21,058,705

select nbr_ip_addr , count(*) from tbl_relation group by nbr_ip_addr order by count(*) desc;
10.81.255.11    76788
10.72.0.202     50299
10.72.9.75      40949
10.72.65.150    38533
10.64.1.176     37262
10.72.65.146    33601
10.72.73.40     33566
.
.
.

select node_ip_addr , count(*) from tbl_relation group by node_ip_addr order by count(*) desc;
10.72.9.75      39310
10.72.0.202     34655
10.81.255.11    25730
10.64.1.176     18443
10.109.64.25    17206
10.72.65.150    16006
10.14.9.185     15831
.
.
.
.

它是一台8核、32gb内存的机器，所有这些都可供postgres使用。
postgres版本：

PostgreSQL 11.6 (Ubuntu 11.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

以下是postgres设置：

maintenance_work_mem        65536   kB
work_mem                    409600  kB
shared_buffers              393216  8kB
commit_delay                100000
max_wal_size                10240   MB
min_wal_size                1024    MB
effective_io_concurrency    8

select pg_size_pretty (pg_relation_size('tbl_relation'));
-- 1834 MB

对于给定大小的表和配置，这是我们能得到的最好的结果吗？还有其他的索引组合我们可以尝试吗？postgres有什么设置吗？或者其他的查询方式？任何帮助都将不胜感激！提前谢谢！

sql postgresql query-optimization indexing

来源：https://stackoverflow.com/questions/61934156/possibility-for-further-optimization-of-a-bitmap-index-scan-on-inet-columns

2条答案

按热度按时间

0mkxixxg1#

SELECT version();

CREATE TABLE tbl_relation (
    node_ip_addr inet NOT NULL  -- <<-- NOT NULL
    , nbr_ip_addr inet NOT NULL -- <<-- (NULL keyvalues in a junction table make no sense)
        , PRIMARY KEY( node_ip_addr, nbr_ip_addr)       -- <<-- will imply an index 
        , UNIQUE (nbr_ip_addr, node_ip_addr)            -- <<-- will imply an index 
);

ANALYZE tbl_relation;

EXPLAIN
SELECT * FROM tbl_relation
WHERE node_ip_addr IN ('10.14.221.167' , '10.14.9.185')
AND nbr_ip_addr IN ('10.14.221.167' , '10.14.9.185')
        ;

结果：（没有数据，但单索引扫描可能会保留）

DROP SCHEMA
CREATE SCHEMA
SET
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit
(1 row)

CREATE TABLE
ANALYZE
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tbl_relation_nbr_ip_addr_node_ip_addr_key on tbl_relation  (cost=0.15..2.85 rows=1 width=64)
   Index Cond: ((nbr_ip_addr = ANY ('{10.14.221.167,10.14.9.185}'::inet[])) AND (node_ip_addr = ANY ('{10.14.221.167,10.14.9.185}'::inet[])))
(2 rows)

额外：您可以尝试进一步优化使用 CLUSTER ，它将或多或少地对记录进行排序，但这需要定期维护（重新分类），尤其是在表内容经常更改的情况下：

CLUSTER tbl_relation USING tbl_relation_pkey;
-- Or:
-- CLUSTER tbl_relation USING tbl_relation_nbr_ip_addr_node_ip_addr_key;

赞(0）回复(0）举报 2021-08-09

5rgfhyps2#

您可以尝试以下方法：

SELECT *
FROM tbl_relation r
WHERE node_ip_addr = '10.14.221.167'::inet AND nbr_ip_addr IN ('10.14.221.167'::inet, '10.14.9.185'::inet)
UNION ALL
SELECT *
FROM tbl_relation r
WHERE node_ip_addr = '10.14.9.185'::inet AND nbr_ip_addr IN ('10.14.221.167'::inet, '10.14.9.185'::inet)

然后我会尝试一个标准的索引 tbl_relation(node_ip_addr, nbr_ip_addr) .
postgres还不支持对索引进行“跳过扫描”。这应该变成两个直接的索引查找。如果这符合您的性能要求，那么可能有其他方法可以获得类似的计划。

赞(0）回复(0）举报 2021-08-09

我来回答

sql—进一步优化~20m记录表中“inet”列的“位图索引扫描”的可能性

2条答案

相关问题

热门标签

最新问答