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 628093A567A for ; Thu, 2 Dec 2004 02:31:42 +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 52776-10 for ; Thu, 2 Dec 2004 02:31:37 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 9FBC43A563C for ; Thu, 2 Dec 2004 02:31:39 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by www.postgresql.com (Postfix) with ESMTP id 07FAD7398E2 for ; Wed, 1 Dec 2004 20:26:22 +0000 (GMT) Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6731503; Wed, 01 Dec 2004 12:24:11 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: Using "LIMIT" is much faster even though, searching Date: Wed, 1 Dec 2004 12:25:42 -0800 User-Agent: KMail/1.6.2 Cc: "Hyun-Sung, Jang" , Tom Lane References: <41AD4433.1030909@siche.net> <200411302103.51643.josh@agliodbs.com> <41AD5EB3.80201@siche.net> In-Reply-To: <41AD5EB3.80201@siche.net> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Message-Id: <200412011225.42119.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.015 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/36 X-Sequence-Number: 9411 Hyun-Sung, > do you need all of verbose information?? > VACUUM FULL ANALYZE VERBOSE give me a lot of infomation, > so i just cut zipcode parts. Oh, sorry. I meant just "VACUUM FULL ANALYZE VERBOSE zipcode", not the whole database. Should have been clearer. > ==start==================================================================== >=========== INFO: vacuuming "public.zipcode" > INFO: "zipcode": found 0 removable, 47705 nonremovable row versions in > 572 pages > DETAIL: 0 dead row versions cannot be removed yet. OK, looks like you're clean. > I just choose zipcode table for this test. > not only zipcode table but other table also give me same result. > > SELECT * FROM table_name WHERE PK = 'xxx' > > was always slower than > > SELECT * FROM table_name WHERE PK = 'xxx' LIMIT 1 > > when sequence scan . yeah? So? Stop using sequence scan! You've just demonstrated that, if you don't force the planner to use sequence scan, things run at the same speed with or without the LIMIT. So you're causing a problem by forcing the planner into a bad plan. See Andrew's explanation of why it works this way. > ah, why i'm using sequence as PK instead of zip code is > in korea, the small towns doesn't have it's own zipcode > so they share other big city's. > that's why zip code can't be a primary key. > actually, i'm not using sequence to find zipcode. > i made it temporary for this test. That makes sense. --Josh -- --Josh Josh Berkus Aglio Database Solutions San Francisco