Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 640346367AA for ; Mon, 23 Feb 2009 13:53:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65238-02-4 for ; Mon, 23 Feb 2009 13:53:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ringonce.com (zalloo.com [62.213.196.40]) by mail.postgresql.org (Postfix) with ESMTP id 85A4663396A for ; Mon, 23 Feb 2009 13:42:04 -0400 (AST) Received: from mail.securax.org (unknown [212.123.25.145]) by ringonce.com (Postfix) with ESMTPS id 9212010C806B for ; Mon, 23 Feb 2009 18:42:01 +0100 (CET) Received: (qmail 1776 invoked by uid 2077); 23 Feb 2009 16:44:25 -0000 Received: from unknown (HELO rt.attractel.com) (89.253.129.130) by mail.securax.org with (DHE-RSA-AES256-SHA encrypted) SMTP; 23 Feb 2009 16:44:25 -0000 Received: (qmail 27054 invoked by uid 89); 23 Feb 2009 17:42:00 -0000 Received: by simscan 1.4.0 ppid: 27048, pid: 27051, t: 0.0106s scanners: clamav: 0.92.1 /m: Received: from unknown (HELO ?10.2.2.158?) (kouber@securax.org@10.2.2.158) by cocaine with ESMTPA; 23 Feb 2009 17:42:00 -0000 Message-ID: <49A2DFFA.8000001@saparev.com> Date: Mon, 23 Feb 2009 19:42:18 +0200 From: Kouber Saparev Reply-To: kouber@saparev.com User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: LIMIT confuses the planner References: <49A295F0.5010405@saparev.com> <49A2A450.20002@archonet.com> In-Reply-To: <49A2A450.20002@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/266 X-Sequence-Number: 32847 Richard Huxton wrote: > Since it's expecting 7914 rows for "kouber" it thinks it will find the > 20 rows you want fairly quickly by just looking backward through the > login_attempt_pkey index. > > Try increasing the stats on the username column. > > ALTER TABLE login_attempt ALTER COLUMN username SET STATISTICS 100; > ANALYZE login_attempt; > > You can try different values of statistics up to 1000, but there's no > point in setting it too high. > Hmmm, that did the trick, thank you. I updated the statistics of the column to 300, so now the query plan changed to: Limit (cost=127.65..127.70 rows=20 width=38) (actual time=0.085..0.086 rows=3 loops=1) -> Sort (cost=127.65..129.93 rows=910 width=38) (actual time=0.084..0.085 rows=3 loops=1) Sort Key: login_attempt_sid Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on login_attempt (cost=7.74..103.44 rows=910 width=38) (actual time=0.075..0.078 rows=3 loops=1) Recheck Cond: ((username)::text = 'kouber'::text) -> Bitmap Index Scan on login_attempt_username_idx (cost=0.00..7.51 rows=910 width=0) (actual time=0.069..0.069 rows=3 loops=1) Index Cond: ((username)::text = 'kouber'::text) Total runtime: 0.114 ms Now the planner believes there're 910 rows, which is a bit closer to the real data: swing=# select avg(length) from (select username, count(*) as length from login_attempt group by username) as freq; avg ---------------------- 491.6087310427555479 (1 row) -- Kouber Saparev http://kouber.saparev.com