database - 如何在 PostgreSQL 9.4 中为基于日期列的全外连接查询索引表？

Question

假设我有 3 个表，其中包含数百万行。

CREATE TABLE blog (
  blog_id integer NOT NULL,
  blog_definition text,
  create_date timestamp without time zone,
  user_id integer,
  CONSTRAINT "Blog_pkey" PRIMARY KEY (blog_id)
);

CREATE TABLE blog_detail (
  blog_detail_id integer NOT NULL,
  blog_id integer,
  blog_header text,
  user_id integer,
  blog_content text,
  create_date timestamp without time zone,
  CONSTRAINT "Blog_Detail_pkey" PRIMARY KEY (blog_detail_id)
);

CREATE TABLE users (
  user_id integer NOT NULL,
  country text,
  user_name text,      
  CONSTRAINT "User_pkey" PRIMARY KEY (user_id)
);

CREATE INDEX blog_create_date_user_id_blog_definition_idx
  ON blog
  USING btree
  (create_date, user_id, blog_definition COLLATE pg_catalog."default");

CREATE INDEX blog_detail_create_date_user_id_blog_content_blog_header_idx
  ON blog_detail
  USING btree
  (create_date, user_id, blog_content COLLATE pg_catalog."default", blog_header COLLATE pg_catalog."default");

CREATE INDEX users_country_user_id_idx
  ON users
  USING btree
  (country COLLATE pg_catalog."default", user_id);

查询就是这样。这个查询用了这些索引花了 35 秒才得到结果。

SELECT b.blog_definition, b.create_date, b.user_id, bd.blog_header,
       bd.blog_content, bd.user_id, bd.create_date 
FROM blog b 
FULL OUTER JOIN blog_detail bd ON b.create_date = bd.create_date 
WHERE CASE
  WHEN b.blog_id IS NULL THEN
    bd.user_id IN (SELECT user_id FROM users WHERE country = 'Greece') 
  WHEN bd.blog_id IS NULL THEN
    b.user_id IN (SELECT user_Id FROM users WHERE country = 'Greece')
  END
ORDER BY CASE 
  WHEN b.blog_id IS NULL THEN bd.create_date 
  WHEN bd.blog_id IS NULL THEN b.create_date 
  ELSE b.create_date
  END DESC
LIMIT 25;

我需要对 3 个表中的哪些列进行索引(以及哪种索引)以获得最佳查询性能？

解释分析结果:

Limit  (cost=820038.99..820039.06 rows=25 width=50) (actual time=33047.344..33047.348 rows=25 loops=1)
  ->  Sort  (cost=820038.99..832538.93 rows=4999976 width=50) (actual time=33047.341..33047.343 rows=25 loops=1)
        Sort Key: (CASE WHEN (b.blog_id IS NULL) THEN bd.create_date WHEN (bd.blog_id IS NULL) THEN b.create_date ELSE b.create_date END)
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Hash Full Join  (cost=191546.31..678943.27 rows=4999976 width=50) (actual time=3039.060..28832.090 rows=15000000 loops=1)
              Hash Cond: (b.create_date = bd.create_date)
              Filter: CASE WHEN (b.blog_id IS NULL) THEN (hashed SubPlan 1) WHEN (bd.blog_id IS NULL) THEN (hashed SubPlan 2) ELSE NULL::boolean END
              ->  Seq Scan on blog b  (cost=0.00..173529.53 rows=9999953 width=22) (actual time=0.035..2090.918 rows=10000000 loops=1)
              ->  Hash  (cost=91666.89..91666.89 rows=4999989 width=28) (actual time=3003.440..3003.440 rows=5000000 loops=1)
                    Buckets: 8192  Batches: 128  Memory Usage: 2546kB
                    ->  Seq Scan on blog_detail bd  (cost=0.00..91666.89 rows=4999989 width=28) (actual time=0.008..1130.650 rows=5000000 loops=1)
              SubPlan 1
                ->  Index Only Scan using users_country_user_id_idx on users  (cost=0.56..1496.38 rows=41361 width=4) (actual time=0.050..4.007 rows=20000 loops=1)
                      Index Cond: (country = 'Germany'::text)
                      Heap Fetches: 0
              SubPlan 2
                ->  Index Only Scan using users_country_user_id_idx on users users_1  (cost=0.56..1496.38 rows=41361 width=4) (actual time=0.057..4.060 rows=20000 loops=1)
                      Index Cond: (country = 'Germany'::text)
                      Heap Fetches: 0
Planning time: 0.253 ms
Execution time: 33048.583 ms

Answer 1

就像 Couling 对您的问题的评论一样，FULL JOIN 往往会出现索引问题。也就是说，您的查询还有很多需要改进的地方:

SELECT b.blog_definition, create_date, b.user_id, bd.blog_header,
       bd.blog_content, bd.user_id
FROM blog b 
FULL JOIN blog_detail bd USING (create_date) 
WHERE EXISTS 
  (SELECT 1 FROM users
   WHERE country = 'Greece' AND user_id = coalesce(bd.user_id, b.user_id))
ORDER BY create_date DESC
LIMIT 25;

• 当您使用 USING 子句(而不是 ON)执行 JOIN 时，选择中只会包含一个匹配列列表，因此无需使用别名。无论如何，复杂的 ORDER BY 子句是不必要的，因为 b.create_date 和 bd.create_date 由于连接而相等。
• WHERE 过滤器中的 CASE WHEN 子句也可以通过使用 coalesce() 函数和任何一个表的明显条件来避免blog_id 必须有一个值，user_id 也必须有一个值(否则您的查询将失败，因为过滤器将评估为 WHERE NULL)。由于 b.blog_id 是表 blog 的主键，因此它永远不会是 NULL 所以按照同样的逻辑 b.user_id 永远不能为 NULL，您可以用列名替换 coalesce() 函数。但这留给你去思考。如果您查看 EXPLAIN ANALYZE，您会发现同一个子查询被计算了两次(SubPlan 1 和 SubPlan 2)。此查询将只访问表 users 一次。这节省了适当的 4 毫秒!再加上几毫秒，因为子查询比您的代码更快。
• create_date 字段是一个时间戳。只有当两条记录都是在同一 session 中创建的，或者当一条记录中的值是从另一条记录中检索到的，因此它们的值完全 相同。

您在表 blog_detail 上定义了一个索引，但该索引将非常大，因为您包括两个可能很大的文本字段。单独在 create_date 上使用索引会更小(因此磁盘读取更少)并且处理速度更快。