From: Kouber Saparev <kouber@saparev.com>
To: pgsql-performance@postgresql.org
Subject: Re: LIMIT confuses the planner
Date: Mon, 23 Feb 2009 19:42:18 +0200
Message-ID: <49A2DFFA.8000001@saparev.com> (raw)
In-Reply-To: <49A2A450.20002@archonet.com>
References: <49A295F0.5010405@saparev.com>
<49A2A450.20002@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kouber@saparev.com
Subject: Re: LIMIT confuses the planner
In-Reply-To: <49A2DFFA.8000001@saparev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox