Will postgresql generate index automatically?

Question

is there automatic index in Postgresql or need users to create index explicitly? if there is automatic index, how can I view it? thanks.

Answer 1

An index on the primary key and unique constraints will be made automatically. Use CREATE INDEX to make more indexes. To view existing database structure including the indexes, use \d table.

A quick example of generating an index would be:

CREATE INDEX unique_index_name ON table (column);

You can create an index on multiple columns:

CREATE INDEX unique_index_name ON table (column1, column2, column3);

Or a partial index which will only exist when conditions are met:

CREATE INDEX unique_index_name ON table (column) WHERE column > 0;

There is a lot more you can do with them, but that is for the documentation (linked above) to tell you. Also, if you create an index on a production database, use CREATE INDEX CONCURRENTLY (it will take longer, but not lock out new writes to the table). Let me know if you have any other questions.

---

Update:

If you want to view indexes with pure SQL, look at the pg_catalog.pg_indexes table:

SELECT *
FROM pg_catalog.pg_indexes
WHERE schemaname='public'
AND tablename='table';