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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
