From: Pierre C <lists@peufeu.com>
To: Corin <wakathane@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: planer chooses very bad plan
Date: Mon, 12 Apr 2010 12:06:41 +0200
Message-ID: <op.va1npfh6eorkce@immo.peufeu.com> (raw)
In-Reply-To: <4BC23B3E.5020500@gmail.com>
References: <4BC23B3E.5020500@gmail.com>
> 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.
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: lists@peufeu.com, wakathane@gmail.com
Subject: Re: planer chooses very bad plan
In-Reply-To: <op.va1npfh6eorkce@immo.peufeu.com>
* 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