1

In PostgreSQL,

  • when I create a table, and doesn't create any index for it, will PostgreSQL automatically create some default index for the table?

  • When I later update and query the table several times, will PostgreSQL be smart enough to automatically create an index for me based on how and how often I update and query the table?

  • If not, what commands in PostgreSQL can help me manually choose an index that will improve the performance of the table?

Thanks.

Improve this question
3
  • I know the question is about Postgres, but for SQL Server there is a decent similar question Tools for Identifying Needed Indexes Commented Jul 12, 2018 at 0:33
  • Thanks. Would be nice to know if PostgreSQL has similar tools. Commented Jul 12, 2018 at 1:02
  • similar to stackoverflow.com/q/23876479 Commented Aug 31, 2023 at 8:46

1 Answer 1

Reset to default
1

No database engine will create indexes on its own. Indexes have an important impact on performance (when modifying the records), and it's your role to know and calculate the performance gain or drop to take a clever/informed decision. The only index which is automatically created is the PrimaryKey index.

The only thing your database engine will be "smart" about, is when and how to use the indexes which already exists. This is called the query optimizer, and it bases its decision on complex algorithms and internal statistics.

There are tools to analyze how the database works to suggest some indexes. But the best, and simplest way, is to use an EXPLAIN.

https://www.postgresql.org/docs/9.5/static/sql-explain.html

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks. EXPLAIN explains but does not suggest.
If you don't mind, In terms of your previous job and current plan, what knowledge do you consider outdated and what do you consider up to date and desirable? I am asking as a self learner also interested in Java, databases (PostgreSQL in particular), JDBC, Hibernate, JavaEE, Spring, etc.
This is a broad question which goes way out of StackOverflow's purpose. Look for JPA maybe. If your question has been answered, would you mind to accept it? Thank you.
In addition to primary keys, UNIQUE and EXCLUDE constraints are also backed by an index
@GuillaumeF. My account was deleted by accident, before I could accept your reply. My apology.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.