Query tuning: partitioning, DISTINCT ON, and indexing

From: Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Query tuning: partitioning, DISTINCT ON, and indexing
Date: 2013-06-21 01:24:46
Message-ID: CAOtHd0BA8+zzA5npf+-C=PZaB-=OiSMDSHPPNf0ZSOscSrJkNA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm trying to optimize a query on a partitioned table. The schema looks
like this:

CREATE TABLE observations(
ts timestamptz NOT NULL DEFAULT now(),
type text NOT NULL,
subject uuid NOT NULL,
details json NOT NULL
);

The table is partitioned by ts (right now I have ~300 1h partitions, which
I know is pushing it; I'm looking at daily instead, though for what it's
worth, an unpartitioned table doesn't seem to perform much better here).
The query is:

SELECT
DISTINCT ON (type) ts, type, details
FROM
observations
WHERE
subject = '...'
ORDER BY
type, ts DESC;

The cardinality of "type" is fairly low (~3 right now, probably less than
two dozen in the foreseeable future). Most types are likely to have an
entry with a very recent timestamp (most likely in the latest partition),
but I can't depend on that.

I've tried a number of different index combinations of ts, type, and
subject (both composite and individual indexes), but nothing seems to run
especially quickly. The table has a fresh ANALYZE. I'm running 9.2.4. I've
posted [1] an EXPLAIN ANALYZE for the version with an index on (subject,
type, ts). Any thoughts?

[1]: http://explain.depesz.com/s/mnI

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message bricklen 2013-06-21 04:13:11 Re: Query tuning: partitioning, DISTINCT ON, and indexing
Previous Message Andrew Dunstan 2013-06-20 21:32:34 Re: PostgreSQL settings for running on an SSD drive