索引是否保留在派生表上？

PostgreSQL可以在子查询中“保留”索引的意义不大，因为重写器通常可以简化和重组查询，以便直接在基表上进行操作。

在这种情况下，查询变得不必要地复杂；子查询可以完全消除，这使它成为一个简单的自连接。

SELECT count(*) 
FROM  chromosomal_positions file_1 
INNER JOIN chromosomal_positions other_files
ON ( file_1.chromosome_id = other_files.chromosome_id 
     AND file_1.position = other_files.position ) 
WHERE file1.variant_file_id = 1
AND   other_files.variant_file_id != 1;

因此在(chromosome_id, position)此处建立索引显然非常有用。

您可以随时尝试索引的选择和用法，explain analyze以确定查询计划程序的实际操作。例如，如果我想看看是否：

那我会

CREATE INDEX cp_f_c_p ON chromosomal_positions(file_id, chromosome_id , position);

-- Planner would prefer seqscan because there's not really any data;
-- force it to prefer other plans.
SET enable_seqscan = off;

EXPLAIN SELECT count(*) 
FROM (
  SELECT * 
  FROM   chromosomal_positions 
  WHERE  file_id = 1
) AS file_1 
INNER JOIN (
  SELECT * 
  FROM   chromosomal_positions 
  WHERE  file_id != 1
) AS other_files 
ON ( file_1.chromosome_id = other_files.chromosome_id 
     AND file_1.position = other_files.position ) 

并得到结果：

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=78.01..78.02 rows=1 width=0)
   ->  Hash Join  (cost=29.27..78.01 rows=1 width=0)
         Hash Cond: ((chromosomal_positions_1.chromosome_id = chromosomal_positions.chromosome_id) AND (chromosomal_positions_1."position" = chromosomal_positions."position"))
         ->  Bitmap Heap Scan on chromosomal_positions chromosomal_positions_1  (cost=14.34..48.59 rows=1930 width=8)
               Filter: (file_id <> 1)
               ->  Bitmap Index Scan on cp_f_c_p  (cost=0.00..13.85 rows=1940 width=0)
         ->  Hash  (cost=14.79..14.79 rows=10 width=8)
               ->  Bitmap Heap Scan on chromosomal_positions  (cost=4.23..14.79 rows=10 width=8)
                     Recheck Cond: (file_id = 1)
                     ->  Bitmap Index Scan on cp_f_c_p  (cost=0.00..4.23 rows=10 width=0)
                           Index Cond: (file_id = 1)
(11 rows)

（在explain.depesz.com上查看）

显示虽然它将使用索引，但实际上仅将其用于第一列。它不会使用其余的，只是在上过滤file_id。因此，以下索引同样好，并且维护起来更小且更便宜：

CREATE INDEX cp_file_id ON chromosomal_positions(file_id);

果然，如果创建此索引，则Pg将首选它。因此，不，您提出的索引似乎没有用，除非计划者认为在此数据规模上不值得使用该索引，并且可能选择在具有更多数据的完全不同的计划中使用它。您确实必须对真实数据进行测试才能确定。

相比之下，我建议的索引：

CREATE INDEX cp_ci_p ON chromosomal_positions (chromosome_id, position);

至少在空的虚拟数据集上用于查找id = 1的染色体位置。我怀疑规划者会避免在比这个更大的数据集上嵌套循环。再说一遍，您实际上只需要尝试一下即可看到。

（顺便说一句，如果计划者被迫具体化一个子查询，那么它就不会“保留派生表上的索引”，即具体化的子查询。这对于WITH（CTE）查询术语尤其重要，后者始终是具体化的。）