4 ms·

#4 seems like a zealous overgeneralisation. JOINs get a lot of crap. Yes, it's no secret that large joins on huge amounts of data can often be very intensive.

#4 seems like a zealous overgeneralisation. JOINs get a lot of crap. Yes, it's no secret that large joins on huge amounts of data can often be very intensive. From that does not follow, however, the exhortation to simply avoid them categorically. That's a little too much blanket statement for me. For instance, joins are often used in situations where there is a numeric type column that refers to a very small table of enumerated values that have a textual or other translation, and there is a need to produce the latter in a single query. There's nothing wrong with that join from a performance standpoint, even for very large values of n.

gtuhl 17y ago
JOINs rightly get a lot of crap - they are massive performance bottlenecks if you are working with large tables. Having a single awkward example does not detract from that. I'll add to this by noting the post does say "For smaller tables it doesn’t matter but as tables get bigger avoid joining when you can" which is sound advice. I'd say joining scales up to perhaps a million or two rows unless you have a lot of RAM (you can see join spills to disk in the EXPLAIN ANALYZE output). I often am working with tables in the 10-30 million row range so my perspective is probably a little slanted towards the negative.