Skip site navigation (1) Skip section navigation (2)

Re: Performance on large, append-only tables

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To:
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance on large, append-only tables
Date: 2012-02-10 16:26:54
Message-ID: CAGTBQpZiWptgfApcbBB0=EGctWacUayfQRz701WKxF2AttKg9Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Feb 10, 2012 at 1:19 PM, David Yeu <david(dot)yeu(at)skype(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: David YeuDate: 2012-02-10 16:45:39
Subject: Re: Performance on large, append-only tables
Previous:From: David YeuDate: 2012-02-10 16:19:57
Subject: Re: Performance on large, append-only tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group