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