Creating indexes for PostgreSQL database

[Robert Huber]

Hi All

I read in the manuals and web about applying indexes to tables for the PostgreSQL db V8.2.5. But I still can't seem to find an overview for that.

Is there a tool which helps in creating indexes for tables? Or do I really have to create each index manually?

PostgreSQL doesn't create indexes for primary keys as Sybase does, is that assumption correct?

Best regards, Robert

[swingman]

Hi Robert,

I use pgAdminIII to create indexes. I suppose if I had to create a lot of indexes, I would check out the code pgAdmin generates and manually write a script.

[ROCLASI]

Hi Robert,

I read in the manuals and web about applying indexes to tables for the PostgreSQL db V8.2.5. But I still can't seem to find an overview for that.

What kind of overview are you looking for?
This page in the manual has all the information you need for creating an index.

Is there a tool which helps in creating indexes for tables? Or do I really have to create each index manually?

Like Christian already mentioned you can use PgAdminIII but you can also use any other GUI/CLI tool that is out there.
I like to use DbWrench myself.
Of course you could also use the RawSQL plugin in Servoy.

PostgreSQL doesn't create indexes for primary keys as Sybase does, is that assumption correct?

Good question. I had to look for it but with a Primary Key constraint you also have an index on it in PostgreSQL. So your assumption is incorrect
(TIP: when you use psql you can see all indices of your database with the command \di, use \d indexname to see the details )

Hope this helps.

[Robert Huber]

Hi Robert

Thanks for the hint to the manual. Saying overview, I meant the various possibilities for creating indexes, not the specific syntax. But it seems there is no help in the form of an analyzing and creation tool in pgAdminIII. Is there one in the DBWrench app?

Creating a primary key constraint like

ALTER TABLE stations ADD PRIMARY KEY (id);

after creating the table with a stmt like

CREATE TABLE stations (altitude int4 , company_id int4 , ..., valid_sts_ht int4 NOT NULL);

does not create a primary key index.
But may be I didn't understand you correctly and you meant something different.

Thanks for your help and best regards, Robert

PS: Did you receive my example where I can't suppress the horizontal slider?

[ROCLASI]

Hi Robert

Thanks for the hint to the manual. Saying overview, I meant the various possibilities for creating indexes, not the specific syntax. But it seems there is no help in the form of an analyzing and creation tool in pgAdminIII. Is there one in the DBWrench app?

There is no wizard or something for analyzing if a column needs an index or not like Sybase Central has. Although I believe something like that is planned.
As for creating/managing indices you can use DbWrench and other tools.

Creating a primary key constraint like

ALTER TABLE stations ADD PRIMARY KEY (id);

after creating the table with a stmt like

CREATE TABLE stations (altitude int4 , company_id int4 , ..., valid_sts_ht int4 NOT NULL);

does not create a primary key index.
But may be I didn't understand you correctly and you meant something different.

It implicitly creates an index for the primary key. I just tested it with psql and PgAdminIII.
The only thing is that PgAdminIII doesn't show the index as such. Psql however does show the index.
Actually psql explicitly tells you it does:

Code: Select all

# ALTER TABLE stations ADD PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "stations_pkey" for table "stations"
ALTER TABLE

(also EXPLAIN SELECT * FROM stations WHERE id=?; will show it uses the index )

PS: Did you receive my example where I can't suppress the horizontal slider?

Nope, did you email it to me or did you use the private mailbox on this forum?


Hope this helps.

[Robert Huber]

Hi Robert

With psql, I also see the primary key indexes! Hmmm, have you got any idea why this is NOT shown in pgAdminIII? I don't see any "logic" for that to hide.

Thanks for the tip anyway, one seems to use psql for certain tasks.

PostgreSQL doesn't create indexes for primary keys as Sybase does, is that assumption correct?

Good question. I had to look for it but with a Primary Key constraint you also have an index on it in PostgreSQL. So your assumption is incorrect
(TIP: when you use psql you can see all indices of your database with the command \di, use \d indexname to see the details )

I have sent the example to the eMail you defined in your Servoy forum profile. Should I send it to another address?

PS: Did you receive my example where I can't suppress the horizontal slider?

Nope, did you email it to me or did you use the private mailbox on this forum?

Best regards, Robert

[patrick]

I guess the reason to not show the index is because it is not of any relevance. You cannot delete an index on a primary key, it is always there if you define a primary key, so why should you even bother...

[Robert Huber]

Salut Patrick

I agree with you that it is not needed to show pk indexes, my problem was only that I didn't "see" them being created (automatically) - so, as soon one knows that it's ok. I still don't see any advantage if one doesn't see them in pgAdminIII, after all we are developers so we should know what's in our app .-)

Best regards, Robert

I guess the reason to not show the index is because it is not of any relevance. You cannot delete an index on a primary key, it is always there if you define a primary key, so why should you even bother...

[Robert Huber]

Hi Robert

Sent you the example again to the eMail address of your profile.

Thanks, Robert

Hi Robert

PS: Did you receive my example where I can't suppress the horizontal slider?

Nope, did you email it to me or did you use the private mailbox on this forum?


Hope this helps.

[huber]

Hi Patrick

After quite a while, I can give you a good reason why to bother After searching for performance bottlenecks in various different areas, I did beside lot's of other trials a REINDEX INDEX of primary keys, with quite stunning results for some tables
That's in our project, this may vary, but now I am quite sure one can and should not forget about primary key index, and for that matter foreign key indexes as well.
Don't know other developers experiences, but would be interesting to hear about them.

Regards, Robert

I guess the reason to not show the index is because it is not of any relevance. You cannot delete an index on a primary key, it is always there if you define a primary key, so why should you even bother...

[omar]

Hi,

I like to use NaviCat Premium as my database design tool. It supports all the major databases, data modeling and lot's more. There's a Windows, a Mac and a Linux version. (I don't have shares )

The PK for example, on the example_data/customers table is shown like this:

navicat.png (81.82 KiB) Viewed 6458 times

Kind regards,