Re: Speeding up select distinct

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Re: Speeding up select distinct
Дата
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3412A7651@Herge.rcsinc.local
обсуждение исходный текст
Ответ на Speeding up select distinct  (Laurent Martelli <laurent@aopsys.com>)
Список pgsql-performance
> 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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Rod Taylor
Дата:
Сообщение: Re: Speeding up select distinct
Следующее
От: Greg Stark
Дата:
Сообщение: Re: cpu_tuple_cost