Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8886B9FB260 for ; Wed, 10 Jan 2007 01:56:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 21866-04 for ; Wed, 10 Jan 2007 01:55:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9BC9F9FB25C for ; Wed, 10 Jan 2007 01:55:52 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0A5tTmD000375; Wed, 10 Jan 2007 00:55:29 -0500 (EST) To: "Gregory S. Williamson" cc: "Plugge, Joe R." , pgsql-performance@postgresql.org Subject: Re: Horribly slow query/ sequential scan In-reply-to: <71E37EF6B7DCC1499CEA0316A256832802B3EB9B@loki.wc.globexplorer.net> References: <32CC45C5BDC57241B9BCEF37AC0C5DB5021844E6@OMAEXMB02.corp.westworlds.com> <71E37EF6B7DCC1499CEA0316A256832802B3EB9B@loki.wc.globexplorer.net> Comments: In-reply-to "Gregory S. Williamson" message dated "Tue, 09 Jan 2007 15:40:51 -0800" Date: Wed, 10 Jan 2007 00:55:29 -0500 Message-ID: <374.1168408529@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/128 X-Sequence-Number: 22729 "Gregory S. Williamson" writes: > As Joe indicated, there is indeed an Informix explain, appended below my signature ... > select > w.appid,w.rate,w.is_subscribed,sum(w.hits) AS Hits ,sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total,sum(w.hits) * w.rate AS ByHits, > sum(w.sius) * w.rate AS BYIUS > from bill_rpt_work w, billing_reports b > where w.report_id in > (select b.report_id from billing_reports where b.report_s_date = '2006-09-30') > and (w.client_id = '227400001' or w.client_id = '2274000010') > group by 1,2,3 > order by 1,2,3 > Estimated Cost: 3149 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By Group By > 1) informix.b: INDEX PATH > (1) Index Keys: report_s_date (Serial, fragments: ALL) > Lower Index Filter: informix.b.report_s_date = datetime(2006-09-30) year to day > 2) informix.w: INDEX PATH > Filters: (informix.w.client_id = '227400001' OR informix.w.client_id = '2274000010' ) > (1) Index Keys: report_id (Serial, fragments: ALL) > Lower Index Filter: informix.w.report_id = informix.b.report_id > NESTED LOOP JOIN > 3) informix.billing_reports: SEQUENTIAL SCAN (First Row) > NESTED LOOP JOIN (Semi Join) Interesting! "Semi join" is the two-dollar technical term for what our code calls an "IN join", viz a join that returns at most one copy of a left-hand row even when there's more than one right-hand join candidate for it. So I think there's not any execution mechanism here that we don't have. What seems to be happening is that Informix is willing to flatten the sub-SELECT into an IN join even though the sub-SELECT is correlated to the outer query (that is, it contains outer references). I'm not sure whether we're just being paranoid by not doing that, or whether there are special conditions to check before allowing it, or whether Informix is wrong ... regards, tom lane