From: Hannu Krosing <hannu@2ndquadrant.com>
To: Corin <wakathane@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: planer chooses very bad plan
Date: Mon, 12 Apr 2010 00:25:12 +0300
Message-ID: <1271021112.21800.21.camel@hvost> (raw)
In-Reply-To: <4BC23B3E.5020500@gmail.com>
References: <4BC23B3E.5020500@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hannu@2ndquadrant.com, wakathane@gmail.com
Subject: Re: planer chooses very bad plan
In-Reply-To: <1271021112.21800.21.camel@hvost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox