Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0F0E99FA43B for ; Fri, 9 Feb 2007 18:38:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 24514-05 for ; Fri, 9 Feb 2007 18:38:44 -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 CC83D9FA110 for ; Fri, 9 Feb 2007 18:38:46 -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 l19McdLP005626; Fri, 9 Feb 2007 17:38:40 -0500 (EST) To: "George Pavlov" cc: pgsql-sql@postgresql.org Subject: Re: huge disparities in =/IN/BETWEEN performance In-reply-to: <8C5B026B51B6854CBE88121DBF097A868160D3@ehost010-33.exch010.intermedia.net> References: <8C5B026B51B6854CBE88121DBF097A86815F42@ehost010-33.exch010.intermedia.net> <7377.1170986068@sss.pgh.pa.us> <8C5B026B51B6854CBE88121DBF097A86815F81@ehost010-33.exch010.intermedia.net> <20070209021414.GU24069@alvh.no-ip.org> <14273.1170993005@sss.pgh.pa.us> <8C5B026B51B6854CBE88121DBF097A868160D3@ehost010-33.exch010.intermedia.net> Comments: In-reply-to "George Pavlov" message dated "Fri, 09 Feb 2007 11:21:41 -0800" Date: Fri, 09 Feb 2007 17:38:39 -0500 Message-ID: <5625.1171060719@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/127 X-Sequence-Number: 27461 "George Pavlov" writes: > I somehow wish I could tell the optimizer to > first figure out which stuff_ids are related to the user_id that is > being asked for and then look ONLY those up in the stuff_events table > using the index on stuff_id. This is not really an optimizer problem, or at least not just an optimizer problem. The type of plan I think you are wishing for is what the source code calls a "nestloop with inner index scan", and that terminology should tip you off that it's only considered when the inner relation is just a simple indexscannable table. GROUP BY subqueries need not apply :-(. I've been speculating recently about how this situation might be improved, but I fear it will require nontrivial executor changes along with planner changes. The executor's present mechanism for passing variable values from the outer plan to the inner is a hack that only really works for indexscans. I got it to work for inheritance cases too, recently, but that's about as far as it can be pushed. I think it might be possible to get rid of it and use the more-recently-invented subplan parameter mechanism, but I haven't worked out the details. (And I know that the Greenplum crowd would like to get rid of subplan parameters, so I'm not sure this idea will go over well anyway.) The planner changes needed will be pretty wide-ranging too, likely. This might happen for 8.4 but I wouldn't promise it for 8.3. regards, tom lane