Re: Performance on large, append-only tables

On Fri, Feb 10, 2012 at 1:19 PM, David Yeu <david.yeu@skype.net> wrote:
>> => EXPLAIN ANALYZE SELECT  "lines".* FROM "lines" WHERE (lines.deleted_at IS NULL) AND ("lines".group_id = ?) AND
(id< ?) ORDER BY id DESC LIMIT 20 OFFSET 0; 

Interesting...

Do you have many "deleted" rows?
Do you always filter them out like this?

Because in that case, you can add the condition to the indices to
exclude deleted rows from the index. This is a big win if you have
many deleted rows, only the index expression has to be exactly the
same (verbatim) as the one used in the query.

That, and an index on "(group_id, created_at) where (deleted_at IS
NULL)" to catch the sorted by date kind of query, and you'll be done I
think.