Menu
I just created a table in Postgres and received a notice message that I do not completely understand regarding implicit indexes and sequences. Any clarification would be appreciated.
my_database=# CREATE TABLE sites
my_database-# (
my_database(# site_id_key serial primary key,
my_database(# site_url VARCHAR(255),
my_database(# note VARCHAR(255),
my_database(# type INTEGER,
my_database(# last_visited TIMESTAMP
my_database(# ) ;
NOTICE: CREATE TABLE will create implicit sequence "sites_site_id_key_seq" for serial column "sites_to_search.site_id_key"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sites_pkey" for table "sites_to_search"
CREATE TABLE
245
"Implicit" indexes have the advantage that they are part of the table definition. "Explicit" indexes have the advantage that they are created explicitly. Other than the definition, the use of the index in queries should be the same.
An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.
PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.
Description. CREATE INDEX constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance).
Certain things in PostgreSQL are handled by indexes and sequences. These include serial and bigserial types, unique constraints, and primary keys. These are not implicit in PostgreSQL (so the notice is a little misleading) but rather implicit in the DDL that PostgreSQL is running.
In other words, the indexes and sequences are just normal indexes and sequences. They are just automatically created by PostgreSQL in order to provide the guarantees and features you have requested in your DDL.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
