Re: Speeding up select distinct

From: Merlin Moncure
Subject: Re: Speeding up select distinct
Date: ,
Msg-id: 6EE64EF3AB31D5448D0007DD34EEB3412A7651@Herge.rcsinc.local
(view: Whole thread, Raw)
In response to: Speeding up select distinct  (Laurent Martelli)
List: pgsql-performance

Tree view

Speeding up select distinct  (Laurent Martelli, )
 Re: Speeding up select distinct  (PFC, )
  Re: Speeding up select distinct  (Laurent Martelli, )
   Re: Speeding up select distinct  (Greg Stark, )
 Re: Speeding up select distinct  (Rod Taylor, )
  Re: Speeding up select distinct  (Laurent Martelli, )
   Re: Speeding up select distinct  (Rod Taylor, )
 Re: Speeding up select distinct  ("Merlin Moncure", )
  Re: Speeding up select distinct  (Laurent Martelli, )
 Re: Speeding up select distinct  ("Merlin Moncure", )

> 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 by date:

From: David Brown
Date:
Subject: Re: multi-column index
From: Greg Stark
Date:
Subject: Re: Speeding up select distinct