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
