Re: Query optimization

"Ryan Riehle" <rkr@buildways.com> writes:
> I have this query that is taking way too long for what I want to do:

The sort step seems to be taking the bulk of the time, so the
micro-optimization answer would be to boost sort_mem, and maybe also
take a second look at your datatypes (perhaps se.businessserviceid
is a low-performance type such as numeric?)

Given the disparity of the rowcounts in the tables, another possibility
is to write something like

select distinct cs.contractcode from contractservices cs
where cs.businessserviceid = 167
and not exists
(select 1 from serviceevents se where
 se.businessserviceid = cs.businessserviceid and se.contractcode = cs.contractcode )

If you have an index on (se.businessserviceid, se.contractcode) then
the EXISTS should result in one index probe into se for each cs
row, which'll probably be faster than the mergejoin approach.

Also, do you actually need the "distinct" (formerly "group by")?
If there are quite a few duplicates then it might be better to
factor the query so that the distinct elimination happens before
the EXISTS test.

            regards, tom lane