Understanding simple queries and the cost model

In this section, we will get started with indexes. To show how things work, some test data is needed. The following code snippet shows how data can be created easily:

test=# CREATE TABLE t_test (id serial, name text); 
CREATE TABLE 
test=# INSERT INTO t_test (name) SELECT 'hans' 
   FROM generate_series(1, 2000000); 
INSERT 0 2000000 
test=# INSERT INTO t_test (name) SELECT 'paul' 
   FROM generate_series(1, 2000000); 
INSERT 0 2000000

In the first line, a simple table is created. Two columns are used: an autoincrement column that just keeps creating numbers and a column that will be filled with static values.

In all, 4 million rows have been added:

test=# SELECT name, count(*) FROM t_test GROUP BY 1; 
 name | count  
------+--------- 
 hans | 2000000 
 paul | 2000000 
(2 rows) 

These 4 million rows have some nice properties. IDs are ascending and there are only two distinct names.

Let's run a simple query now:

test=# \timing 
Timing is on. 
test=# SELECT * FROM t_test WHERE id = 432332; 
   id   | name  
--------+------ 
 432332 | hans 
(1 row) 

Time: 176.949 ms 

In this case, the timing command will tell psql to show the runtime of a query. Note that this is not the real execution time on the server, but the time measured by psql. In case of very short queries, network latency can be a substantial part of the total time, so this has to be taken into account.