Query tuning: partitioning, DISTINCT ON, and indexing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux