Thread: primary key index
Is a primary key an implicit index, or if I want to join on that field is it also worthwhile to make an explicit index?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/14/07 03:27, Sim Zacks wrote: > Is a primary key an implicit index, or if I want to join on that field > is it also worthwhile to make an explicit index? On-line documentation is such a wonderful resource... http://www.postgresql.org/docs/7.3/interactive/sql-createtable.html PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, it is not necessary to create an explicit index for primary key columns. (Yes, I purposefully used the doc set of an ancient version.) - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGSBOrS9HxQb37XmcRAlhTAKDFekzal8p33SB5oW10NyTiQtkNQACeIXpP Y8JuW+MjqIxWqZl86PsIxuw= =TxAn -----END PGP SIGNATURE-----
It is mixture of looking and knowing where to look. I read the section on indexes where it says that PostGreSQL creates a unique index, but I didn't see an index on the table so I didn't know if it was implicit or if for some reason my primary keys weren't becoming indexes. I didn't see the create table documentation which states that it is not necessary to explicitly create them. Thank you for your help. > PostgreSQL automatically creates a unique index when a unique constraint > or a primary key is defined for a table. The index covers the columns that > make up the primary key or unique columns (a multicolumn index, if appropriate), > and is the mechanism that enforces the constraint. Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 05/14/07 03:27, Sim Zacks wrote: >> Is a primary key an implicit index, or if I want to join on that field >> is it also worthwhile to make an explicit index? > > On-line documentation is such a wonderful resource... > > http://www.postgresql.org/docs/7.3/interactive/sql-createtable.html > > PostgreSQL automatically creates an index for each unique > constraint and primary key constraint to enforce the uniqueness. > Thus, it is not necessary to create an explicit index for > primary key columns. > > (Yes, I purposefully used the doc set of an ancient version.) > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Give a man a fish, and he eats for a day. > Hit him with a fish, and he goes away for good! > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFGSBOrS9HxQb37XmcRAlhTAKDFekzal8p33SB5oW10NyTiQtkNQACeIXpP > Y8JuW+MjqIxWqZl86PsIxuw= > =TxAn > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
2007/5/14, Sim Zacks <sim@compulab.co.il>:
> I read the section on indexes where it says that PostGreSQL creates a unique
> index, but I didn't see an index on the table so I didn't know if it was implicit or if
> for some reason my primary keys weren't becoming indexes.
You are supposed to see those indexes. Try "\d tablename" in psql. It
should give you a bunch of information, including something like:
Indexes:
"tablename_pkey" PRIMARY KEY, btree (keyfieldname)
Where tablename is your table's name, and keyfieldname the name of the
column that is your PK.
greetings,
Nicolas
--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Really?
toll_archive=# create table snaggle (
toll_archive(# foo integer,
toll_archive(# bar integer,
toll_archive(# primary key (foo));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"snaggle_pkey" for table "snaggle"
CREATE TABLE
Time: 240.183 ms
toll_archive=# \d snaggle
Table "public.snaggle"
Column | Type | Modifiers
- --------+---------+-----------
foo | integer | not null
bar | integer |
Indexes:
"snaggle_pkey" PRIMARY KEY, btree (foo)
On 05/14/07 04:32, Sim Zacks wrote:
> It is mixture of looking and knowing where to look. I read the section
> on indexes where it says that PostGreSQL creates a unique index, but I
> didn't see an index on the table so I didn't know if it was implicit or
> if for some reason my primary keys weren't becoming indexes. I didn't
> see the create table documentation which states that it is not necessary
> to explicitly create them.
>
> Thank you for your help.
>
>> PostgreSQL automatically creates a unique index when a unique
>> constraint or a primary key is defined for a table. The index covers
>> the columns that
>> make up the primary key or unique columns (a multicolumn index, if
> appropriate),
>> and is the mechanism that enforces the constraint.
>
>
> Ron Johnson wrote:
> On 05/14/07 03:27, Sim Zacks wrote:
>>>> Is a primary key an implicit index, or if I want to join on that field
>>>> is it also worthwhile to make an explicit index?
>
> On-line documentation is such a wonderful resource...
>
> http://www.postgresql.org/docs/7.3/interactive/sql-createtable.html
>
> PostgreSQL automatically creates an index for each unique
> constraint and primary key constraint to enforce the uniqueness.
> Thus, it is not necessary to create an explicit index for
> primary key columns.
>
> (Yes, I purposefully used the doc set of an ancient version.)
>
- --
Ron Johnson, Jr.
Jefferson LA USA
Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFGSF4HS9HxQb37XmcRAm8WAJsGNYwp6zlrQxjPoUsikIlzr9p3/ACbBuwI
5gITI5aqS+vkpsxVZqHrpiE=
=hJp/
-----END PGP SIGNATURE-----