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?
