is there automatic index in Postgresql or need users to create index explicitly? if there is automatic index, how can I view it? thanks.
2
-
2Please read the manual: postgresql.org/docs/current/static/ddl.htmluser330315– user3303152014-05-26 19:21:58 +00:00Commented May 26, 2014 at 19:21
-
1PostgreSQL will automatically create an index for the primary key of a table, when you create the table. On the command line, it will tell you that it creates it and it's name.okaram– okaram2014-05-26 19:22:47 +00:00Commented May 26, 2014 at 19:22
Add a comment
|
1 Answer
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';
8 Comments
user3419945
ok, when I create index on primary key manually, I found the query plan is different from no explicit index plan. why does this happen?
Sam
You would need to update your question with both query plans. There are many index types, so this may be causing differences (PostgreSQL won't use an index if it doesn't think it will be more efficient than a sequential scan).
user3419945
Can I view the index by SQL query? as I'm using pgadmin debuging and can not use \d.
user3419945
Thanks @Sam, I created an index (CREATE INDEX full_edge_idx ON full_edge (edge);), there is no result if I run the up query. If I run (CREATE INDEX full_edge_idx ON full_edge (edge);) again, it shows ERROR: relation "full_edge_idx" already exists SQL state: 42P07.
pozs
Exclusion constraints also adds its index automatically (though not commonly used) postgresql.org/docs/current/static/…
|