Subject | Re: question about optimizing group by |
---|---|
Author | woodsmailbox |
Post date | 2008-11-21T19:09:48Z |
The reason I posted this is because the above is a very common query,
usually involving many more fields than just company_name added to the
group-by clause, so I have a strong incentive to optimize this kind of
query. I'm far from being an expert at interpreting execution plans,
but just empirically I got quite different results with the different
methods above, so I thought somebody might give me a hint about how
the optimizer handles this particular problem.
Why not?
The idea of the first() aggregate function was to _avoid_ reading an
index and avoid doing a scan and sort, and instead non-
deterministically choose whatever row in the group, since they're all
the same.
usually involving many more fields than just company_name added to the
group-by clause, so I have a strong incentive to optimize this kind of
query. I'm far from being an expert at interpreting execution plans,
but just empirically I got quite different results with the different
methods above, so I thought somebody might give me a hint about how
the optimizer handles this particular problem.
> ...I doubt there is much difference between these options, I wouldnever choose your alternative 1
Why not?
>..., don't (yet) see any benefit of theoretical alternative 3 (Iexpect it to be very similar to alternative 1 if the field is indexed)
The idea of the first() aggregate function was to _avoid_ reading an
index and avoid doing a scan and sort, and instead non-
deterministically choose whatever row in the group, since they're all
the same.