3. Handling Indexes | Troubleshooting PostgreSQL

Solving common problems

After this brief introduction to indexes, the focus of this chapter will shift to troubleshooting. The most common challenges related to indexing and troubleshooting will be described in this section.

Managing foreign keys

One of the most important issues causing trouble and confusion when it comes to indexing has to do with foreign keys. Consider the following example:

test=# CREATE TABLE t_person (id   int   PRIMARY KEY, 
          name text);
CREATE TABLE
test=# CREATE TABLE t_car (car_id   int, 
    person_id   int REFERENCES t_person (id),
     info     text);
CREATE TABLE

It is necessary to have a unique index irrespective of a primary key on the t_person table. However, we often forget to index the other side of the foreign key (in our case, the t_car table). Just imagine looking up all cars owned by a certain person. You would definitely want an index scan on the t_car table to speed up this query.

Note

Keep in mind that PostgreSQL does not create an automatic index...

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.

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Solving common problems

Managing foreign keys

Note

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Solving common problems

Managing foreign keys

Note

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