Re:Use SQL subqueries to count distinct 50x faster
by jeffdavis on Friday January 24, 2014 @11:34AM
(#7115740)
"As a general recommendation I advise database developers to avoid exactly this sort of "outsmart the planner""
Probably wise in general. This particular case is peculiar because postgres doesn't optimize distinct aggregates at all (see my other comment: https://news.ycombinator.com/item?id=7115707 ), so it's possible that the rewritten query is actually more optimizable than the original.
The original query won't adapt to changing data sizes or distributions as well as the rewritten one.
This is not fundamental, nor universal. It's a specific limitation of postgres, so I still agree with your general advice. And postgres may fix this, so your advice may prove to be true even in this situation in the long run.
Probably wise in general. This particular case is peculiar because postgres doesn't optimize distinct aggregates at all (see my other comment: https://news.ycombinator.com/item?id=7115707 ), so it's possible that the rewritten query is actually more optimizable than the original.
The original query won't adapt to changing data sizes or distributions as well as the rewritten one.
This is not fundamental, nor universal. It's a specific limitation of postgres, so I still agree with your general advice. And postgres may fix this, so your advice may prove to be true even in this situation in the long run.