Message-ID: <4523FBFB.5020007@refractions.net>
Date: Wed, 04 Oct 2006 11:22:51 -0700
From: Graham Davis <gdavis@refractions.net>
Reply-To: gdavis@refractions.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
MIME-Version: 1.0
To: Tobias Brox <tobias@nordicbet.com>
Cc: "To:pgsql-performance"@postgresql.org
Subject: Re: Multi-key index not beeing used - bug?
References: <20061004181243.GB9871@oppetid.no>
In-Reply-To: <20061004181243.GB9871@oppetid.no>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks Tobias.  The difference here though, is that in terms of your 
database I am doing a query to select the most recent transaction for 
EACH user at once, not just for one user.  If I do a similar query to 
yours to get the last transaction for a single user, my query is fast 
like yours.  It's when I'm doing a query to get the results for all 
users at once that it is slow.  If you try a query to get the most 
recent transaction of all useres at once you will run into the same 
problem I am having.

Graham.


Tobias Brox wrote:

>Look at this:
>
>NBET=> explain select * from account_transaction where users_id=123456 order by created desc limit 10;
>                                                                QUERY PLAN
>-------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.00..27.40 rows=10 width=213)
>   ->  Index Scan Backward using account_transaction_on_user_and_timestamp on account_transaction  (cost=0.00..1189.19 rows=434 width=213)
>         Index Cond: (users_id = 123456)
>(3 rows)
>
>NBET=> explain select * from account_transaction where users_id=123456 order by created desc, id desc limit 10;
>                                                          QUERY PLAN
>------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=1114.02..1114.04 rows=10 width=213)
>   ->  Sort  (cost=1114.02..1115.10 rows=434 width=213)
>         Sort Key: created, id
>         ->  Index Scan using account_transaction_by_users_id on account_transaction  (cost=0.00..1095.01 rows=434 width=213)
>               Index Cond: (users_id = 123456)
>(5 rows)
>
>In case the explains doesn't explain themself good enough: we have a
>transaction table with ID (primary key, serial), created (a timestamp)
>and a users_id.  Some of the users have generated thousands of
>transactions, and the above query is a simplified version of the query
>used to show the users their last transactions.  Since we have a large
>user base hammering our servers with this request, the speed is
>significant.
>
>We have indices on the users_id field and the (users_id, created)-tuple.
>
>The timestamp is set by the application and has a resolution of 1 second
>- so there may easily be several transactions sharing the same
>timestamp, but this is an exception not the rule.  I suppose the
>developers needed to add the ID to the sort list to come around a bug,
>but still prefering to have the primary sorting by created to be able to
>use the index.  One workaround here is to order only by id desc and
>create a new index on (users_id, id) - but I really don't like adding
>more indices to the transaction table.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>  
>


-- 
Graham Davis
Refractions Research Inc.
gdavis@refractions.net