Subject: Re: planer chooses very bad plan
From: Hannu Krosing <hannu@2ndquadrant.com>
To: Corin <wakathane@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4BC23B3E.5020500@gmail.com>
References: <4BC23B3E.5020500@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Date: Mon, 12 Apr 2010 00:25:12 +0300
Message-ID: <1271021112.21800.21.camel@hvost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sun, 2010-04-11 at 23:12 +0200, Corin wrote:
> Hi,
> 
> I'm having a query where the planer chooses a very bad plan.
> 
> 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
> 
> "Limit  (cost=0.00..1557.67 rows=10 width=78) (actual 
> time=0.096..2750.058 rows=5 loops=1)"
> "  ->  Index Scan Backward using telegrams_pkey on telegrams  
> (cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052 
> rows=5 loops=1)"
> "        Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) 
> OR ((user_id = 508933) AND (NOT user_deleted)))"
> "Total runtime: 2750.124 ms"

You could check if creating special deleted_x indexes helps

do

CREATE INDEX tgrm_deleted_recipent_index ON telegrams(recipient_id)
 WHERE recipient_deleted=FALSE;

CREATE INDEX tgrm_deleted_user_index ON telegrams(user_id) 
 WHERE user_deleted=FALSE;

(if on live system, use "CREATE INDEX CONCURRENTLY ...")

-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training