X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 59D73B439A9 for ; Mon, 7 Jul 2003 20:58:40 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 05214-05 for ; Mon, 7 Jul 2003 17:58:28 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 5191CB439D3 for ; Mon, 7 Jul 2003 17:57:44 -0300 (ADT) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by www.postgresql.com (Postfix) with ESMTP id 3D6E3CF6F5F for ; Mon, 7 Jul 2003 15:23:21 -0300 (ADT) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id F00D6138374; Mon, 7 Jul 2003 14:22:01 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 19Zacj-00086X-00; Mon, 07 Jul 2003 14:22:01 -0400 To: pgsql-performance@postgresql.org Subject: optimizer picks smaller table to drive nested loops? From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 07 Jul 2003 14:22:00 -0400 Message-ID: <873chi2o53.fsf@stark.dyndns.tv> Lines: 34 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/131 X-Sequence-Number: 2424 Someone asked a hypothetical question about how to retrieve all records of a table twice in SQL. It got me thinking about whether there was a way to do this efficiently. "Obviously" if you do it using the UNION ALL approach postgres isn't going to do two separate scans, doing it otherwise would be quite hard. However using the join approach it seems postgres ought to be able to do a single sequential scan and return every tuple it finds twice. It doesn't do this: slo=> explain analyze select * from region, (select 1 union all select 2) as x; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..11162.00 rows=5534 width=108) (actual time=0.13..541.19 rows=5534 loops=1) -> Subquery Scan x (cost=0.00..2.00 rows=2 width=0) (actual time=0.03..0.08 rows=2 loops=1) -> Append (cost=0.00..2.00 rows=2 width=0) (actual time=0.02..0.05 rows=2 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1) -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1) -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) -> Seq Scan on region (cost=0.00..2813.00 rows=2767 width=104) (actual time=0.03..123.44 rows=2767 loops=2) Total runtime: 566.24 msec (9 rows) Wouldn't it be faster to drive the nested loop the other way around? (I'm also a bit puzzled why the optimizer is calculating that 2,813 * 2 = 5,534) This is tested on 7.3. I haven't tried CVS yet. -- greg