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 8372A52896 for ; Fri, 22 Jul 2005 11:39:58 -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 01385-06 for ; Fri, 22 Jul 2005 14:39:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A57145288B for ; Fri, 22 Jul 2005 11:39:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j6MEdvbu029474; Fri, 22 Jul 2005 10:39:57 -0400 (EDT) To: Dawid Kuroczko Cc: Postgresql Performance Subject: Re: Planner doesn't look at LIMIT? In-reply-to: <758d5e7f0507220210bfa4978@mail.gmail.com> References: <758d5e7f0507220210bfa4978@mail.gmail.com> Comments: In-reply-to Dawid Kuroczko message dated "Fri, 22 Jul 2005 11:10:05 +0200" Date: Fri, 22 Jul 2005 10:39:57 -0400 Message-ID: <29473.1122043197@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/347 X-Sequence-Number: 13588 Dawid Kuroczko writes: > qnex=# EXPLAIN SELECT * FROM log NATURAL JOIN useragents LIMIT 1; > Limit (cost=15912.20..15912.31 rows=1 width=272) > -> Hash Join (cost=15912.20..5328368.96 rows=47044336 width=272) > If I set enable_hashjoin=false: > qnex=# EXPLAIN ANALYZE SELECT * FROM log NATURAL LEFT JOIN useragents LIMIT 1; > Limit (cost=0.00..3.07 rows=1 width=272) (actual time=74.214..74.216 > rows=1 loops=1) > -> Nested Loop Left Join (cost=0.00..144295895.01 rows=47044336 > width=272) (actual time=74.204..74.204 rows=1 loops=1) This is quite strange. The nestloop plan definitely should be preferred in the context of the LIMIT, considering that it has far lower estimated cost. And it is preferred in simple tests for me. It seems there must be something specific to your installation that's causing the planner to go wrong. Can you develop a self-contained test case that behaves this way for you? I recall we saw a similar complaint a month or two back, but the complainant never followed up with anything useful for tracking down the problem. regards, tom lane