Thread: Indices

Indices

From
sharmad@goatelecom.com
Date:
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


Re: Indices

From
Stephan Szabo
Date:
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...)




What happens if you delete a row containing a BLOB?

From
Terrence Brannon
Date:
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.



Re: What happens if you delete a row containing a BLOB?

From
Stephan Szabo
Date:
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.




Re: What happens if you delete a row containing a BLOB?

From
"Christopher Kings-Lynne"
Date:
> 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



how could a foreign key ever be NULL?

From
Terrence Brannon
Date:
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?



Re: how could a foreign key ever be NULL?

From
Bruce Momjian
Date:
> 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
 


Re: how could a foreign key ever be NULL?

From
Oliver Elphick
Date:
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  

Re: how could a foreign key ever be NULL?

From
Stephan Szabo
Date:
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."