Re: planer chooses very bad plan

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