Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA19251 for ; Tue, 21 Sep 1999 10:16:35 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA21559; Tue, 21 Sep 1999 10:15:32 -0400 (EDT) To: Daniel Lopez cc: pgsql-sql@hub.org Subject: Re: [SQL] Subselect performance In-reply-to: Your message of Mon, 20 Sep 1999 19:24:29 +0000 () <199909201924.VAA10959@atm9.com.dtu.dk> Date: Tue, 21 Sep 1999 10:15:32 -0400 Message-ID: <21555.937923332@sss.pgh.pa.us> From: Tom Lane Daniel Lopez writes: > $list = select d from c > select b from a where b in ( $list ) > is 5 seconds > select b from a where b in (select d from c) > is 3 minutes!! (although it should be at least as fast as a)! Not necessarily. Your first example is depending on the fact that the "list" (number of values selected from c) is short. Try it with 10000 or 100000 values from c, if you want to see the backend crash ;-) The second case works OK even if the sub-select result is large, because it re-executes the sub-select for each row from a (essentially, rescanning c to see if b is matched). However, this means the runtime is proportional to the product of the number of rows in a and c. Ugh. Try rewriting your query as a join: select a.b from a, c where a.b = c.d (you might want "select distinct" here, if b can match many rows from d). If the system can't figure out anything better than a nested-loop join, then it'll probably end up taking about the same amount of time, but this form at least allows the possibility of a smarter join method. I believe we have a TODO list item to perform this sort of transformation automatically when the sub-select is of a form that allows it. We need to get the left/outer join stuff working first in order to have an exact match of the behavior. regards, tom lane