Re: Speeding up select distinct

> I just wished there was a means to fully automate all this and render
> it transparent to the user, just like an index.
>
>   Merlin> Voila!  Merlin p.s. normalize your data always!
>
> I have this:
>
> pictures(
>     PictureID serial PRIMARY KEY,
>     Owner integer NOT NULL REFERENCES users,
>     [...]);
> CREATE TABLE users (
>     UserID serial PRIMARY KEY,
>     Name character varying(255),
>     [...]);
>
> Isn't it normalized ?

try:
select * from users where UserID in (select pictureId from pictures);
select * userid from users intersect select pictureid from pictures;
select distinct userid, [...] from users, pictures where user userid =
pictureid)

if none of these give you what you want then you can solve this with a
new tble, picture_user using the instructions I gave previously.

Not sure if your data is normalized, but ISTM you are over-using
surrogate keys.  It may not be possible, but consider downgrading ID
columns to unique and picking a natural key.  Now you get better benefit
of RI and you can sometimes remove joins from certain queries.

Rule: use natural keys when you can, surrogate keys when you have to.
Corollary: use domains for fields used in referential integrity.

Merlin