Thread: Indices
Hi, Having to manny indices to a table,does it effect the performance of a table.Like a table having following structure Name : First name, Second Name, Third name, Age, Address. Is it good here to have multiple indices like on only first name, only second name,only third name and all other combination of first ,second and third name or the performance increases when we have only 1 indice of all first ,second and third name Thanks -Sharmad
On Tue, 11 Dec 2001 sharmad@goatelecom.com wrote: > Hi, > Having to manny indices to a table,does it effect the performance of a table.Like > a table having following structure Generally it'll affect the speed of transactions that write to the table. > > Name : > First name, > Second Name, > Third name, > Age, > Address. > > Is it good here to have multiple indices like on only first name, only second > name,only third name and all other combination of first ,second and third name > or the performance increases when we have only 1 indice of all first ,second > and third name It mostly depends on what you're searching for in your queries. PostgreSQL will attempt to use multi-column indexes for prefixing columns (so an index on (a,b,c) can be used for queries that constrain a, but not for queries that don't, a query that constrains a and c probably only uses the index to constrain a, etc...)
If you delete a row with a BLOB, does it automatically lo_unlink the blob? This was not clear when reading the section Large Objects (BLOBS) in the Momjian book.
On Sun, 16 Dec 2001, Terrence Brannon wrote: > If you delete a row with a BLOB, does it automatically lo_unlink > the blob? IIRC, no, you'd need to make triggers in order to do that. One thing to be careful with is making sure that you don't have multiple references to the large object before unlinking it.
> If you delete a row with a BLOB, does it automatically lo_unlink > the blob? No, I believe it does not - you need to run an 'lo_delete' command separately. In Postgres 7.1+ though, I think it's easier to just use the 'bytea' binary large string type. It's there in 7.1, but 7.2 has introduced many new operators for it. Chris
The Momjian book states: A single-column foreign key is either NULL or matches a primary key But the problem I have with this statement is that a primary key cannot be null or duplicate. And since the foreign key references a value that is neither null or duplicate, how could it ever be NULL?
> The Momjian book states: > > A single-column foreign key is either NULL or matches a primary key > > But the problem I have with this statement is that a primary key > cannot be null or duplicate. And since the foreign key references > a value that is neither null or duplicate, how could it ever be > NULL? Uh, you could have a foreign key row that doesn't match a primary key row, and is hence NULL, can't you? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Mon, 2001-12-17 at 11:38, Terrence Brannon wrote: > The Momjian book states: > > A single-column foreign key is either NULL or matches a primary key > > But the problem I have with this statement is that a primary key > cannot be null or duplicate. And since the foreign key references > a value that is neither null or duplicate, how could it ever be > NULL? If the foreign key is NULL, it doesn't reference anything. If this is not a valid condition, the column should be declared as NOT NULL. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For I say, through the grace given unto me, to every man that is among you: Do not think of yourself more highly than you ought, but rather think of yourself with sober judgement, in accordance with the measure of faithGod has given you." Romans 12:3
On Mon, 17 Dec 2001, Terrence Brannon wrote: > The Momjian book states: > > A single-column foreign key is either NULL or matches a primary key > > But the problem I have with this statement is that a primary key > cannot be null or duplicate. And since the foreign key references > a value that is neither null or duplicate, how could it ever be > NULL? First, you don't need to reference a primary key. Any unique constraint should work (11.8 referential constraint definition, Syntax rules 2a), "If the <referenced table and columns> specifies a <reference column list>, then the set of column names of that <reference column list> shall be equal to the set of column names in the unique columns of a unique constraint of the referenced table." You're probably confusing it with the behavior that if no column list is given it goes to the primary key columns (11.8 SR2b I believe). As for the NULL part, 4.10.2 Table Constraints, (ignoring the not implemented match partial), "A referential constraint is satisfied if one of the following conditions is true, depending on the <match option> specified in the <referntial constraint definition>:- If no <match type> was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or thevalue of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some rowof the referenced table.- If MATCH FULL was specified then, for each row R1 of the refer- encing table, either thevalue of every referencing column in R1 shall be a null value, or the value of every referencing column in R1 shallnot be null and there shall be some row R2 of the referenced table such that the value of each referencing col- umn in R1 is equal to the value of the corresponding referenced column in R2."