On Thu, Jan 21, 2010 at 9:27 PM, Allen Johnson <akjohnson78@gmail.com> wrote:
> What I noticed in the production query was that ~1000ms was spent on
> sorting alone. The hack query reduced that to ~400ms. I should also
> note that there was plenty of work_mem and that the sort was not
> hitting disk.
>
The "hack" form actually seems far worse to me but I guess it depends
on the actual data layout.
Have you tried something like this which I would expect to be far faster:
select
ct.name, c.lname, c.fname, c.mname,
c.email, c.address1, c.address2,
c.city, c.state, c.zip_code,
(select count(*) from attachments where attachments.contact_id = c.id)
from
contacts c
inner join contact_types ct on (ct.code = c.contact_type_code)
where
c.company_id = 1
order by
ct.name, c.lname, c.fname, c.mname;
The question arises why Postgres can't automatically detect that this
query is equivalent. That might come when we start implementing the
"functional dependency" stuff from the standard and can determine that
the group by list uniquely identifies a row from the first join.
Currently we don't do that kind of analysis.
--
greg