Message-ID: <435E03E0.2040107@archonet.com>
Date: Tue, 25 Oct 2005 11:07:28 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: Markus Wollny <Markus.Wollny@computec.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange planner decision on quite simple select
References: <2266D0630E43BB4290742247C891057508383E8E@dozer.computec.de>
In-Reply-To: <2266D0630E43BB4290742247C891057508383E8E@dozer.computec.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Markus Wollny wrote:
> Hello!
> 
> I've got a table BOARD_MESSAGE (message_id int8, thread_id int8, ...)
> with pk on message_id and and a non_unique not_null index on thread_id.
> A count(*) on BOARD_MESSAGE currently yields a total of 1231171 rows,
> the planner estimated a total of 1232530 rows in this table. I've got
> pg_autovacuum running on the database and run an additional nightly
> VACUUM ANALYZE over it every night.
> 
> I've got a few queries of the following type:
> 
> select          * 
>                                 from PUBLIC.BOARD_MESSAGE 
>                                 where THREAD_ID = 3354253 
>                                 order by        MESSAGE_ID asc 
>                                 limit           20 
>                                 offset          0; 
> 
> 
> There are currently roughly 4500 rows with this thread_id in
> BOARD_MESSAGE. Explain-output is like so:
> 
>                                                       QUERY PLAN 
> 
> ------------------------------------------------------------------------
> ---------------------------------------------- 
>  Limit  (cost=0.00..3927.22 rows=20 width=1148) 
>    ->  Index Scan using pk_board_message on board_message
> (cost=0.00..1100800.55 rows=5606 width=1148) 
>          Filter: (thread_id = 3354253) 
> (3 rows) 
> 
> I didn't have the patience to actually complete an explain analyze on
> that one - I cancelled the query on several attempts after more than 40
> minutes runtime. Now I fiddled a little with this statement and tried
> nudging the planner in the right direction like so:

Hmm - it shouldn't take that long. If I'm reading this right, it's 
expecting to have to fetch 5606 rows to match thread_id=3354253 the 20 
times you've asked for. Now, what it probably doesn't know is that 
thread_id is correlated with message_id quite highly (actually, I don't 
know that, I'm guessing). So - it starts at message_id=1 and works 
along, but I'm figuring that it needs to reach message_id's in the 3-4 
million range to see any of the required thread.

Suggestions:
1. Try "ORDER BY thread_id,message_id" and see if that nudges things 
your way.
2. Keep #1 and try replacing the index on (thread_id) with 
(thread_id,message_id)

--
   Richard Huxton
   Archonet Ltd