join with redundant results VS simpler join plus multiple selects

From: WireSpot <wirespot(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: join with redundant results VS simpler join plus multiple selects
Date: 2008-11-20 11:53:58
Message-ID: b2d4b0380811200353q6514c651s2a49d53f6520342a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a schema with galleries, people and images. Each person has a
bunch of private images. People can join any number of galleries and
can publish any of their images to the galleries they join (or not).

I'd like to retrieve a data set where for a given gallery id I get all
the people AND all the images they've published to that gallery.

I can do this in two ways.
1) Do a join that will give me the people that belong to said gallery,
then loop in the code and do simple selects to retrieve images in that
gallery for each of them.
2) Do a join between all three tables. The end result will have as
many rows as total images for all the people in the gallery.
Obviously, there's going to be redundant data, since a person's info
will be repeated for each image.

Which is better in terms of performance? I used EXPLAIN ANALYZE and
actual queries and it seems to suggest that option 2, while returning
redundant info, is faster.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message WireSpot 2008-11-20 11:56:50 Re: Prepared statement already exists
Previous Message Sam Mason 2008-11-20 11:49:16 Re: date range query help