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?
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 |