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 1B99E8B9C51 for ; Mon, 7 Feb 2005 17:04:12 +0000 (GMT) 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 85201-01 for ; Mon, 7 Feb 2005 17:04:04 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B172B8B9B64 for ; Mon, 7 Feb 2005 17:03:59 +0000 (GMT) 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 j17H3uvh027756; Mon, 7 Feb 2005 12:03:56 -0500 (EST) To: "Joost Kraaijeveld" Cc: "Pgsql-Performance (E-mail)" Subject: Re: Retry: Is this possible / slow performance? In-reply-to: References: Comments: In-reply-to "Joost Kraaijeveld" message dated "Mon, 07 Feb 2005 17:37:53 +0100" Date: Mon, 07 Feb 2005 12:03:56 -0500 Message-ID: <27755.1107795836@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: 200502/116 X-Sequence-Number: 10445 "Joost Kraaijeveld" writes: > Two queries: one with "set enable_seqscan = on" , the other with "set enable_seqscan = off". The first query lasts 59403 ms, the second query 31 ms ( the desc order variant has the same large difference: 122494 ms vs. 1297 ms). (for the query plans see below). The reason for the difference is that the mergejoin plan has a much lower startup cost than the hash plan, and since you're only fetching 100 rows the startup cost is dominant. IIRC the planner does make some allowance for this effect when preparing a DECLARE CURSOR plan (ie, it puts some weight on startup cost rather than considering only total cost) ... but it's not so optimistic as to assume that you only want 100 out of an estimated 1 million+ result rows. The best solution is probably to put a LIMIT into the DECLARE CURSOR, so that the planner can see how much you intend to fetch. regards, tom lane