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 know is how many pages (blocks of 8 KB each) the table has, and how many tuples (rows) the query planner believes the table to have. As autovacuum is turned off, that information wouldn...