Re: join with redundant results VS simpler join plus multiple selects

WireSpot wrote:
> On Thu, Nov 20, 2008 at 15:05, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
>> That's probably going to be the case. PostgreSQL won't need to read the
>> redundant info in from disk each time, and relative to the image data it's
>> going to be pretty small. By doing it all in one join you're avoiding the
>> overhead of all those network round trips (if on a network), statement
>> preparation and planning, etc etc etc. Additionally, PostgreSQL is probably
>> going to be using a join plan that's much more efficient than anything
>> you'll get by looping over each user and asking for images.
>
> How about if the subset of images for each user is randomized? As in
> ORDER BY RANDOM() LIMIT 3. I'm guessing that will put somewhat of a
> cramp on the big join scenario and perhaps it becomes better to have
> the RANDOM() in the small individual selects?

I'm not even sure how you'd achieve that (exactly 3 randomly selected
images per user) in with a single query. Then again, it's stupidly late
here, so my brain may not be working. Any chance you can post a query
that shows what you're doing?

--
Craig Ringer