X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F36915E37CE for ; Sun, 15 Aug 2004 14:21:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40359-02 for ; Sun, 15 Aug 2004 17:21:35 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0522A5E3639 for ; Sun, 15 Aug 2004 14:21:35 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7FHLUPl028004; Sun, 15 Aug 2004 13:21:31 -0400 (EDT) To: Michael Fuhr Cc: pgsql-performance@postgresql.org Subject: Re: Slow joins against set-returning functions In-reply-to: <20040815160014.GA85211@winnie.fuhr.org> References: <20040815160014.GA85211@winnie.fuhr.org> Comments: In-reply-to Michael Fuhr message dated "Sun, 15 Aug 2004 10:00:14 -0600" Date: Sun, 15 Aug 2004 13:21:30 -0400 Message-ID: <28003.1092590490@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/238 X-Sequence-Number: 7874 Michael Fuhr writes: > Is the planner doing something wrong here? Hard to see how it can be very smart with no idea about what the function is going to return :-(. I'd say that the mergejoin plan is actually a good choice given the limited amount of info, because it has the least degradation when the input varies from what you expected. Those "better" nestloop plans could easily be very far worse, if the function returned more than a trivial number of rows. The reason the two mergejoin cases differ so much is that the scan of the other relation can stop as soon as we've exhausted the function output. Evidently scanning to key 10 doesn't traverse much of stuff_pkey while scanning to key 100000 does. The planner is aware of that effect, but with no information about the maximum key value to be expected from the function scan, it can't apply the knowledge. regards, tom lane