Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group