These are a few details of how a global index works:

Creation of global indexes has the following limitations:

Usage of global indexes has the following limitations:

The pgpro_gbtree extension is included in Postgres Pro Enterprise. To enable pgpro_gbtree, create the extension using the following query:

CREATE EXTENSION pgpro_gbtree;

The following example illustrates usage of a global index.

-- Creating the pgpro_gbtree extension
CREATE EXTENSION pgpro_gbtree;

-- Creating a partitioned table with three partitions
CREATE TABLE t_int (i int PRIMARY KEY, v int, x int) PARTITION BY RANGE (i);
CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (0) TO (100);
CREATE TABLE t_int_2 PARTITION OF t_int FOR VALUES FROM (100) TO (200);
CREATE TABLE t_int_3 PARTITION OF t_int FOR VALUES FROM (200) TO (300);

-- Creating a global index for the v column
CREATE INDEX t_int_v ON t_int USING gbtree (v);

-- Creating a normal index for the x column
CREATE INDEX t_int_x ON t_int (x);

-- Query plan for a condition on the column with the global index
EXPLAIN SELECT * FROM t_int WHERE v > 100;

-- Query plan for a condition on the column with the normal index
EXPLAIN SELECT * FROM t_int WHERE x > 100;

The example produces the following output:

CREATE EXTENSION
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using t_int_v on t_int  (cost=0.12..8.14 rows=0 width=0)
   Index Cond: (v > 100)
(2 rows)

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Append  (cost=9.42..93.97 rows=2040 width=12)
   ->  Bitmap Heap Scan on t_int_1  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_1_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
   ->  Bitmap Heap Scan on t_int_2  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_2_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
   ->  Bitmap Heap Scan on t_int_3  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_3_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
(13 rows)

The result of this example shows that with the global index, the index scan is performed for the entire partitioned table, while with the normal index, the index scan is performed for each partition individually, and this search can be slow when the number of partitions is high.