O'Reilly logo

Troubleshooting PostgreSQL by Hans-Jürgen Schönig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Avoiding trouble with indexes

Indexes are not always a solution to the problem; they can also be the problem by themselves. The following example outlines a common pitfall. It should be avoided at all costs:

test=# CREATE TABLE t_test (id int, x text);
CREATE TABLE
test=# INSERT INTO t_test SELECT x, 'house' 
  FROM generate_series(1, 10000000) AS x;
INSERT 0 10000000
test=# CREATE INDEX idx_x ON t_test (x);
CREATE INDEX

Before taking a look at the way the index is used, it makes sense to inspect the size of the table as well as the size of the indexes:

test=# SELECT
   pg_size_pretty(pg_relation_size('t_test')), 
  pg_size_pretty(pg_relation_size('idx_x'));
 pg_size_pretty | pg_size_pretty 
----------------+----------------
 422 MB         | 214 MB
(1 row)

The table ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required