pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAre Indices automatically generated for primary keys?
10+ messages / 6 participants
[nested] [flat]
* Are Indices automatically generated for primary keys?
@ 2010-08-18 13:51 Clemens Eisserer <linuxhippy@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Clemens Eisserer @ 2010-08-18 13:51 UTC (permalink / raw)
To: pgsql-performance
Hi,
Are indices for columns marked with "PRIMARY KEY" automatically generated by
postgresql, or do I have to do it manually?
The question might seem dumb, I ask because I remember from working with
MySQL it generates indices automatically in this case.
Thank you in advance, Clemens
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 13:54 hubert depesz lubaczewski <depesz@depesz.com>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: hubert depesz lubaczewski @ 2010-08-18 13:54 UTC (permalink / raw)
To: Clemens Eisserer <linuxhippy@gmail.com>; +Cc: pgsql-performance
On Wed, Aug 18, 2010 at 03:51:22PM +0200, Clemens Eisserer wrote:
> Hi,
>
> Are indices for columns marked with "PRIMARY KEY" automatically generated by
> postgresql, or do I have to do it manually?
> The question might seem dumb, I ask because I remember from working with
> MySQL it generates indices automatically in this case.
they are generated automatically.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 13:59 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: Kevin Grittner @ 2010-08-18 13:59 UTC (permalink / raw)
To: Clemens Eisserer <linuxhippy@gmail.com>; pgsql-performance
Clemens Eisserer <linuxhippy@gmail.com> wrote:
> Are indices for columns marked with "PRIMARY KEY" automatically
> generated by postgresql, or do I have to do it manually?
If you look at the documentation page for CREATE TABLE, you'll see
the following:
| 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. (See CREATE INDEX for more information.)
http://www.postgresql.org/docs/current/interactive/sql-createtable.html
There's a lot of information on the page, but if you use your
browser to search for PRIMARY KEY within the page, it's not too hard
to find.
Also, if you create a primary key or a unique constraint on a table,
you should see a notice informing you of the creation of the index,
and its name.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 14:15 Clemens Eisserer <linuxhippy@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 10+ messages in thread
From: Clemens Eisserer @ 2010-08-18 14:15 UTC (permalink / raw)
To: pgsql-performance
Hi,
> they are generated automatically.
Thanks depesz!
The reason why I asked was because pgAdmin doesn't display the
automatically created indices, which confused me.
Thanks, Clemens
PS:
> If you look at the documentation page for CREATE TABLE, you'll see
> the following ..... but if you use your
> browser to search for PRIMARY KEY within the page, it's not too hard
> to find.
Its quite harsh to imply I didn't look for documentation.
I looked at the "Indexes and ORDER BY" which doesn't mention it, or
I've overlook it.
Doesn't make a difference anyway.
> Also, if you create a primary key or a unique constraint on a table,
> you should see a notice informing you of the creation of the index,
> and its name.
I use Hibernate, and it generates the DDL for me.
Even with debug/DDL/SQL-output enabled, I don't get any hint that an
index was created.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 14:24 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2010-08-18 14:24 UTC (permalink / raw)
To: Clemens Eisserer <linuxhippy@gmail.com>; +Cc: pgsql-performance
Clemens Eisserer <linuxhippy@gmail.com> wrote:
> Its quite harsh to imply I didn't look for documentation.
Sorry; I didn't mean to be harsh. PostgreSQL has excellent
documentation, and we strive to make it better all the time.
Sometimes people coming from some other products aren't used to that
-- I was just trying to point you in the direction of being able to
find things in the future, to save you trouble and delay.
> I looked at the "Indexes and ORDER BY" which doesn't mention it,
> or I've overlook it.
> Doesn't make a difference anyway.
Well, it very much does make a difference, because when someone
makes the effort to find something in our documentation, and in
spite of their best efforts they can't, we tend to consider that a
bug in the documentation. I'll take a look at the page you
mentioned and see if I can work in a suitable reference. I'm sure
you can see, though, why the *main* place it was documented was the
statement which is generally used to create a primary key.
Thanks for responding with the info on where you looked.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Are Indices automatically generated for primary keys?
@ 2010-08-18 14:33 Clemens Eisserer <linuxhippy@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 10+ messages in thread
From: Clemens Eisserer @ 2010-08-18 14:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi Kevin,
> Sorry; I didn't mean to be harsh.
I also overreacted, sorry about that.
Indeed the documentation is well done, as is the software itself =)
Thanks, Clemens
> Sometimes people coming from some other products aren't used to that
> -- I was just trying to point you in the direction of being able to
> find things in the future, to save you trouble and delay.
>
>> I looked at the "Indexes and ORDER BY" which doesn't mention it,
>> or I've overlook it.
>> Doesn't make a difference anyway.
>
> Well, it very much does make a difference, because when someone
> makes the effort to find something in our documentation, and in
> spite of their best efforts they can't, we tend to consider that a
> bug in the documentation. I'll take a look at the page you
> mentioned and see if I can work in a suitable reference. I'm sure
> you can see, though, why the *main* place it was documented was the
> statement which is generally used to create a primary key.
>
> Thanks for responding with the info on where you looked.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 14:52 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Kevin Grittner @ 2010-08-18 14:52 UTC (permalink / raw)
To: Clemens Eisserer <linuxhippy@gmail.com>; +Cc: pgsql-performance
Clemens Eisserer <linuxhippy@gmail.com> wrote:
> I looked at the "Indexes and ORDER BY" which doesn't mention it
The doesn't seem like an appropriate place to discuss when indexes
are created. Do you think you'd have found a mention in the
Introduction page for indexes? Since we talk about the CREATE INDEX
statement there, it seems reasonable to me to add a mention of where
they are automatically created by constraints, too.
Did you try the documentation index? If so, where did you look?
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 15:23 Thom Brown <thom@linux.com>
parent: Justin Graf <justin@magwerks.com>
0 siblings, 1 reply; 10+ messages in thread
From: Thom Brown @ 2010-08-18 15:23 UTC (permalink / raw)
To: Justin Graf <justin@magwerks.com>; +Cc: pgsql-performance
On 18 August 2010 17:06, Justin Graf <justin@magwerks.com> wrote:
> On 8/18/2010 9:15 AM, Clemens Eisserer wrote:
>> Hi,
>>
>>
>>> they are generated automatically.
>>>
>> Thanks depesz!
>> The reason why I asked was because pgAdmin doesn't display the
>> automatically created indices, which confused me.
>>
>> Thanks, Clemens
>>
> PGAdmin caches all database layout locally, the tree view can get very
> stale. So refresh the treeview with either F5 or right click an item in
> the treeview click refresh to rebuild the list.
>
I don't think PgAdmin will display indexes created by primary keys,
only if indisprimary is false.
--
Thom Brown
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-18 16:06 Justin Graf <justin@magwerks.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 10+ messages in thread
From: Justin Graf @ 2010-08-18 16:06 UTC (permalink / raw)
To: pgsql-performance
On 8/18/2010 9:15 AM, Clemens Eisserer wrote:
> Hi,
>
>
>> they are generated automatically.
>>
> Thanks depesz!
> The reason why I asked was because pgAdmin doesn't display the
> automatically created indices, which confused me.
>
> Thanks, Clemens
>
PGAdmin caches all database layout locally, the tree view can get very
stale. So refresh the treeview with either F5 or right click an item in
the treeview click refresh to rebuild the list.
**snip***
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Are Indices automatically generated for primary keys?
@ 2010-08-25 06:58 Guillaume Lelarge <guillaume@lelarge.info>
parent: Thom Brown <thom@linux.com>
0 siblings, 0 replies; 10+ messages in thread
From: Guillaume Lelarge @ 2010-08-25 06:58 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: Justin Graf <justin@magwerks.com>; pgsql-performance
Le 18/08/2010 17:23, Thom Brown a écrit :
> On 18 August 2010 17:06, Justin Graf <justin@magwerks.com> wrote:
>> On 8/18/2010 9:15 AM, Clemens Eisserer wrote:
>>> Hi,
>>>
>>>
>>>> they are generated automatically.
>>>>
>>> Thanks depesz!
>>> The reason why I asked was because pgAdmin doesn't display the
>>> automatically created indices, which confused me.
>>>
>>> Thanks, Clemens
>>>
>> PGAdmin caches all database layout locally, the tree view can get very
>> stale. So refresh the treeview with either F5 or right click an item in
>> the treeview click refresh to rebuild the list.
>>
>
> I don't think PgAdmin will display indexes created by primary keys,
> only if indisprimary is false.
>
pgAdmin doesn't display indexes for primary keys and unique constraints.
These objects are already displayed in the constraints nodes. The fact
that they use an index to enforce the constraints is an implementation
detail.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-08-25 06:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-18 13:51 Are Indices automatically generated for primary keys? Clemens Eisserer <linuxhippy@gmail.com>
2010-08-18 13:54 ` hubert depesz lubaczewski <depesz@depesz.com>
2010-08-18 13:59 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 14:15 ` Clemens Eisserer <linuxhippy@gmail.com>
2010-08-18 14:24 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 14:33 ` Clemens Eisserer <linuxhippy@gmail.com>
2010-08-18 14:52 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 16:06 ` Justin Graf <justin@magwerks.com>
2010-08-18 15:23 ` Thom Brown <thom@linux.com>
2010-08-25 06:58 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox