X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id B5B16CC161 for ; Mon, 14 Jul 2003 20:58:03 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 90476-02 for ; Mon, 14 Jul 2003 20:58:25 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id BFA89CC050 for ; Mon, 14 Jul 2003 17:57:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6EKwPQ3014111; Mon, 14 Jul 2003 16:58:25 -0400 (EDT) To: Greg Stark Cc: pgsql-performance@postgresql.org Subject: Re: optimizer picks smaller table to drive nested loops? In-reply-to: <87znjhkl3u.fsf@stark.dyndns.tv> References: <873chi2o53.fsf@stark.dyndns.tv> <10251.1058205875@sss.pgh.pa.us> <87znjhkl3u.fsf@stark.dyndns.tv> Comments: In-reply-to Greg Stark message dated "14 Jul 2003 14:40:37 -0400" Date: Mon, 14 Jul 2003 16:58:25 -0400 Message-ID: <14110.1058216305@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/229 X-Sequence-Number: 2522 Greg Stark writes: > Tom Lane writes: >> However, it looks to me like the subquery-scan-outside plan probably >> is the faster one, on both my machine and yours. I get > Woah, that's pretty whacky. It seems like it ought to be way faster to do a > single sequential scan and return two records for each tuple read rather than > do an entire unnecessary sequential scan, even if most or even all of the > second one is cached. The problem is the CPU expense of executing "SELECT 1 UNION SELECT 2" over and over. Doing that for every row of the outer table adds up. We were both testing on relatively small tables --- I suspect the results would be different if the outer table were too large to fit in disk cache. I am not sure why the planner did not choose to stick a Materialize node atop the Subquery Scan, though. It looks to me like it should have considered that option --- possibly the undercharging for Subquery Scan is the reason it wasn't chosen. regards, tom lane