PostgreSQL 10 High Performance - Third Edition

Sample data setup

To create some sample data to demonstrate how indexes work, let's create a table with a simple key/value structure. Each key can be an incrementing number, while the values vary from 0 to 10. It's simple to use generate_series to create data like that:

DROP TABLE IF EXISTS t;
CREATE TABLE t(k serial PRIMARY KEY,v integer);
INSERT INTO t(v) 
 SELECT trunc(random() * 10)
  FROM generate_series(1,100000);

Creating a serial field as a PRIMARY KEY will create a sequence and one index for you, as noted when you run the preceding code:

NOTICE:  CREATE TABLE will create implicit sequence "t_k_seq" for serial column "t.k"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"  

The first valuable thing to ...