2. Indexing and Performance Tuning | PostgreSQL Administration Essentials

Using simple binary trees

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.

Preparing the data

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...

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostgreSQL Administration Essentials

By : Hans-Jürgen Schönig

PostgreSQL Administration Essentials

By: Hans-Jürgen Schönig

Overview of this book

Using simple binary trees

Preparing the data

PostgreSQL Administration Essentials

By : Hans-Jürgen Schönig

PostgreSQL Administration Essentials

By: Hans-Jürgen Schönig

Overview of this book

Using simple binary trees

Preparing the data

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access