一个非常简单的查询是使用UNION DISTINCT.我认为如果四个列中的每个列都有一个单独的索引,那将是最有效的.如果Postgres已经实现了Loose Index Scan优化,那么四个列中的每一列都有一个单独的索引是有效的.所以这个查询效率不高,因为它需要对表进行4次扫描(并且不使用索引)：

-- Query 1. (334 ms,368ms) 
SELECT a AS abcd FROM tablename 
UNION                           -- means UNION DISTINCT
SELECT b FROM tablename 
UNION 
SELECT c FROM tablename 
UNION 
SELECT d FROM tablename ;

另一个是第一个UNION ALL,然后使用DISTINCT.这还需要4次表扫描(并且不使用索引).当值很少时效率不差,并且在我的(非广泛的)测试中,更多的值变得最快：

-- Query 2. (87 ms,117 ms)
SELECT DISTINCT a AS abcd
FROM
  ( SELECT a FROM tablename 
    UNION ALL 
    SELECT b FROM tablename 
    UNION ALL
    SELECT c FROM tablename 
    UNION ALL
    SELECT d FROM tablename 
  ) AS x ;

其他答案提供了使用数组函数或LATERAL语法的更多选项. Jack的查询(187毫秒,261毫秒)具有合理的性能,但AndriyM的查询似乎更有效(125毫秒,155毫秒).它们都对表进行一次顺序扫描,不使用任何索引.

实际上,Jack的查询结果比上面显示的要好一些(如果我们删除了顺序),可以通过删除4个内部区别并仅留下外部区域来进一步改进.

最后,如果 – 且仅当 – 4列的不同值相对较少,您可以使用上面的松散索引扫描页面中描述的WITH RECURSIVE hack / optimization并使用所有4个索引,结果非常快！测试了相同的100K行和大约25个不同的值,分布在4列(仅在2毫秒内运行！),而25K不同的值则是最慢的368毫秒：

-- Query 3.  (2 ms,368ms)
WITH RECURSIVE 
    da AS (
       SELECT min(a) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(a) FROM observations
               WHERE  a > s.n)
       FROM   da AS s  WHERE s.n IS NOT NULL  ),db AS (
       SELECT min(b) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(b) FROM observations
               WHERE  b > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  ),dc AS (
       SELECT min(c) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(c) FROM observations
               WHERE  c > s.n)
       FROM   dc AS s  WHERE s.n IS NOT NULL  ),dd AS (
       SELECT min(d) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(d) FROM observations
               WHERE  d > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  )
SELECT n 
FROM 
( TABLE da  UNION 
  TABLE db  UNION 
  TABLE dc  UNION 
  TABLE dd
) AS x 
WHERE n IS NOT NULL ;

SQLfiddle

总而言之,当不同的值很少时,递归查询是绝对的赢家,而有很多值,我的第二个,杰克(下面的改进版本)和AndriyM的查询是最好的表现者.

延迟添加,第一个查询的变体,尽管有额外的独特操作,但比原始的第一个执行得更好,只比第二个稍差：

-- Query 1b.  (85 ms,149 ms)
SELECT DISTINCT a AS n FROM observations 
UNION 
SELECT DISTINCT b FROM observations 
UNION 
SELECT DISTINCT c FROM observations 
UNION 
SELECT DISTINCT d FROM observations ;

和杰克的改进：

-- Query 4b.  (104 ms,128 ms)
select distinct unnest( array_agg(a)||
                        array_agg(b)||
                        array_agg(c)||
                        array_agg(d) )
from t ;