In this section, you will learn about simple binary trees and how the PostgreSQL optimizer treats the trees. Once you understand the basic decisions taken by the optimizer, you can move on to more complex index types.
Indexing does not change user experience too much, unless you have a reasonable amount of data in your database—the more data you have, the more indexing can help to boost things. Therefore, we have to create some simple sets of data to get us started. Here is a simple way to populate a table:
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 our example, we created a table consisting of two columns. The first column is simply an automatically created integer value. The second column contains the name.
Once the table is created...