PG13中的功能—B树索引中的重复数据删除

postgres=# CREATE TABLE btree_dups AS (SELECT GENERATE_SERIES(1,
1000000)::BIGINT AS val);
SELECT 1000000
postgres=# CREATE INDEX btree_idx ON btree_dups(val);
CREATE INDEX
postgres=# SELECT  c.relname
        , c.relkind        
        , pg_size_pretty( pg_relation_size(c.oid) )
FROM    pg_class c
WHERE   c.relname = 'btree_dups'        
        OR  c.oid IN        
        (            
            SELECT i.indexrelid FROM pg_index i WHERE i.indrelid = 
'btree_dups'::regclass
        );  
  relname   | relkind | pg_size_pretty
------------+---------+---------------- 
btree_dups | r       | 35 MB 
btree_idx  | i       | 21 MB
(2 rows)

postgres=# UPDATE btree_dups SET val = val + 1;
UPDATE 1000000

  relname   | relkind | pg_size_pretty
  ------------+---------+---------------- 
  btree_dups | r       | 104 MB 
  btree_idx  | i       | 86 MB
  (2 rows)

  relname   | relkind | pg_size_pretty
  ------------+---------+---------------- 
  btree_dups | r       | 104 MB 
  btree_idx  | i       | 62 MB
  (2 rows)

postgres=# EXPLAIN SELECT val FROM btree_dups;
                             QUERY PLAN
-------------------------------------------------------------------- 
Seq Scan on btree_dups  (cost=0.00..23275.00 rows=1000000 width=8)
(1 row) 

Time: 2.415 ms
postgres=# DO
postgres-# $$BEGIN
postgres$# PERFORM * FROM btree_dups;
postgres$# END;
postgres$# $$;
DO
Time: 190.101 ms

postgres=# EXPLAIN SELECT val FROM btree_dups;
                             QUERY PLAN                             
-------------------------------------------------------------------- 
Seq Scan on btree_dups  (cost=0.00..23274.00 rows=1000000 width=8)
(1 row) 
Time: 2.221 ms
postgres=# DO
postgres-# $$BEGIN
postgres$# PERFORM * FROM btree_dups;
postgres$# END;
postgres$# $$;
DO
Time: 174.843 ms