Re: optimizing postgres

Hello, Tom.
I don't understand relation between constraints and indexes.
By using EMS PostgreSQL Manager Lite, I created indexes on columns, some of
them are unique values.
But when I open it in PgAdmin, all such "unique" indexes are listed as
constraints and there are no  indexes in Indexes section. When I open it
again in EMS PostgreSQL Manager, they are listed as "Indexes".
Does it mean that I need to create additional indexes on the same columns?
Is "Constrain" index as well?
Thanks,

Zlatko


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <lawpoop@gmail.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, July 13, 2007 3:39 AM
Subject: Re: [GENERAL] optimizing postgres


> lawpoop@gmail.com writes:
>> It turned out he was right for our current set up. When I needed to
>> empty the project table to re-parse data, doing a cascading delete
>> could take up to 10 minutes!
>
> You mean ON CASCADE DELETE foreign keys?  Usually the reason that's
> slow is you forgot to put an index on the referencing column.  PG
> doesn't force you to have such an index, but unless the referenced
> table is nearly static you'll want one.
>
> I too am fairly suspicious of the N-tables-are-faster-than-another-
> key-column mindset, but you'd need to do some actual experimentation
> (with correctly optimized table definitions ;-)) to be sure.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match