Re: Query optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: rkr(at)buildways(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query optimization
Date: 2005-03-18 21:57:51
Message-ID: 10635.1111183071@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Ryan Riehle" <rkr(at)buildways(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Morgan Kita 2005-03-18 22:42:47 Re: Question on simulating Enum Data type
Previous Message Keith Worthington 2005-03-18 21:41:21 output a single and double quote in a string