Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: Corin <wakathane@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: planer chooses very bad plan
References: <4BC23B3E.5020500@gmail.com>
Date: Mon, 12 Apr 2010 12:06:41 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.va1npfh6eorkce@immo.peufeu.com>
In-Reply-To: <4BC23B3E.5020500@gmail.com>
User-Agent: Opera Mail/10.10 (Linux)


> explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933  
> AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE))  
> ORDER BY id DESC LIMIT 10 OFFSET 0

If you need very fast performance on this query, you need to be able to  
use the index for ordering.

Note that the following query will only optimize the first page of results  
in the case you want to display BOTH sent and received telegrams.


- Create an index on (recipient_id, id) WHERE NOT recipient_deleted
- Create an index on (user_id, id) WHERE NOT user_deleted
- Drop redundant indexes (recipient_id) and (user_id)

SELECT * FROM (
SELECT * FROM "telegrams" WHERE recipient_id=508933 AND  
recipient_deleted=FALSE ORDER BY id DESC LIMIT 10
UNION ALL
SELECT * FROM "telegrams" WHERE user_id=508933 AND user_deleted=FALSE  
ORDER BY id DESC LIMIT 10
) AS foo ORDER BY id DESC LIMIT 10;

These indexes will also optimize the queries where you only display the  
inbox and outbox, in which case it will be able to use the index for  
ordering on any page, because there will be no UNION.