PostgreSQL 每周新闻 2020-10-28

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  status TEXT
);

INSERT INTO orders (id, customer_id, status)

SELECT
  i,
  (random()*10000)::INT,
  CASE (random() * 2)::int
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'shipped'
    WHEN 2 THEN 'completed'
  END
    FROM generate_series(1, 1000000) i;

首先，我们在customer_id列上添加二级索引：

CREATE INDEX full_idx ON orders (customer_id);

假设经常查询特定客户的不完整订单。使用示例SELECT查询进行的简单EXPLAIN ANALYZE分析显示，在full_idx中扫描了108行，之后过滤器删除了36行：

EXPLAIN SELECT * FROM orders where customer_id = 1001 AND status != 'completed';

            QUERY PLAN
------------------------------------
 Bitmap Heap Scan on orders
  (actual time=0.041..0.116 rows=72 loops=1)
   Recheck Cond: (customer_id = 1001)
   Filter: (status <> 'completed'::text)
   Rows Removed by Filter: 36
   Heap Blocks: exact=108
   ->  Bitmap Index Scan on full_idx
       (actual time=0.026..0.026 rows=108 loops=1)
  Index Cond: (customer_id = 1001)

通过创建仅索引不完整订单的部分索引，我们可以减少扫描的行数。运行与上面相同的EXPLAIN ANALYZE，将显示在partial_idx中仅扫描满足查询的72行：

CREATE INDEX partial ON orders (customer_id)
  WHERE status != 'completed';
  
EXPLAIN SELECT * FROM orders WHERE
  customer_id = 1001 AND status != 'completed';

            QUERY PLAN
------------------------------------
 Bitmap Heap Scan on orders
  (actual time=0.043..0.100 rows=72 loops=1)
   Recheck Cond: ((customer_id = 1001) AND
                  (status <> 'completed'::text))
   Heap Blocks: exact=72
   ->  Bitmap Index Scan on partial_idx
       (actual time=0.034..0.034 rows=72 loops=1)
  Index Cond: (customer_id = 1001)

本周的提示由CockroachDB的创建者Cockroach Labs赞助。部分索引即将在版本20.2中提供。在此演示中了解有关CockroachDB的信息：“分布式SQL：现代的云原生PostgreSQL。”

Written on October 28, 2020