Обсуждение: Query tuning: partitioning, DISTINCT ON, and indexing

Поиск
Список
Период
Сортировка

Query tuning: partitioning, DISTINCT ON, and indexing

От
Maciek Sakrejda
Дата:
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?

Re: Query tuning: partitioning, DISTINCT ON, and indexing

От
bricklen
Дата:

On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
SELECT
  DISTINCT ON (type) ts, type, details
FROM
  observations
WHERE
  subject = '...'
ORDER BY
  type, ts DESC;

First thing: What is your "work_mem" set to, and how much RAM is in the machine? If you look at the plan, you'll immediately notice the "external merge Disk" line where it spills to disk on the sort. Try setting your work_mem to 120MB or so (depending on how much RAM you have, # concurrent sessions, complexity of queries etc)

Re: Query tuning: partitioning, DISTINCT ON, and indexing

От
Maciek Sakrejda
Дата:
On Thu, Jun 20, 2013 at 9:13 PM, bricklen <bricklen@gmail.com> wrote:

On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
SELECT
  DISTINCT ON (type) ts, type, details
FROM
  observations
WHERE
  subject = '...'
ORDER BY
  type, ts DESC;

First thing: What is your "work_mem" set to, and how much RAM is in the machine? If you look at the plan, you'll immediately notice the "external merge Disk" line where it spills to disk on the sort. Try setting your work_mem to 120MB or so (depending on how much RAM you have, # concurrent sessions, complexity of queries etc)

Good call, thanks, although the in-mem quicksort is not much faster:

                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=471248.30..489392.67 rows=3 width=47) (actual time=32002.133..32817.474 rows=3 loops=1)
   Buffers: shared read=30264
   ->  Sort  (cost=471248.30..480320.48 rows=3628873 width=47) (actual time=32002.128..32455.950 rows=3628803 loops=1)
         Sort Key: public.observations.type, public.observations.ts
         Sort Method: quicksort  Memory: 381805kB
         Buffers: shared read=30264
         ->  Result  (cost=0.00..75862.81 rows=3628873 width=47) (actual time=0.026..1323.317 rows=3628803 loops=1)
               Buffers: shared read=30264
               ->  Append  (cost=0.00..75862.81 rows=3628873 width=47) (actual time=0.026..978.477 rows=3628803 loops=1)
                     Buffers: shared read=30264
...

the machine is not nailed down, but I think I'd need to find a way to drastically improve the plan to keep this in Postgres. The alternative is probably caching the results somewhere else: for any given subject, I only need the latest observation of each type 99.9+% of the time.

Re: Query tuning: partitioning, DISTINCT ON, and indexing

От
bricklen
Дата:
On Thu, Jun 20, 2013 at 10:14 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
On Thu, Jun 20, 2013 at 9:13 PM, bricklen <bricklen@gmail.com> wrote:

On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
SELECT
  DISTINCT ON (type) ts, type, details
FROM
  observations
WHERE
  subject = '...'
ORDER BY
  type, ts DESC;

First thing: What is your "work_mem" set to, and how much RAM is in the machine? If you look at the plan, you'll immediately notice the "external merge Disk" line where it spills to disk on the sort. Try setting your work_mem to 120MB or so (depending on how much RAM you have, # concurrent sessions, complexity of queries etc)

Good call, thanks, although the in-mem quicksort is not much faster:

                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=471248.30..489392.67 rows=3 width=47) (actual time=32002.133..32817.474 rows=3 loops=1)
   Buffers: shared read=30264
   ->  Sort  (cost=471248.30..480320.48 rows=3628873 width=47) (actual time=32002.128..32455.950 rows=3628803 loops=1)
         Sort Key: public.observations.type, public.observations.ts
         Sort Method: quicksort  Memory: 381805kB
         Buffers: shared read=30264
         ->  Result  (cost=0.00..75862.81 rows=3628873 width=47) (actual time=0.026..1323.317 rows=3628803 loops=1)
               Buffers: shared read=30264
               ->  Append  (cost=0.00..75862.81 rows=3628873 width=47) (actual time=0.026..978.477 rows=3628803 loops=1)
                     Buffers: shared read=30264
...

the machine is not nailed down, but I think I'd need to find a way to drastically improve the plan to keep this in Postgres. The alternative is probably caching the results somewhere else: for any given subject, I only need the latest observation of each type 99.9+% of the time.


Did you try an index on (type, ts desc) ? I don't have much else to add at this point, but maybe after posting some more server and table (parent and child) details someone will have an answer for you.

Re: Query tuning: partitioning, DISTINCT ON, and indexing

От
Maciek Sakrejda
Дата:
On Fri, Jun 21, 2013 at 9:08 AM, bricklen <bricklen@gmail.com> wrote:
Did you try an index on (type, ts desc) ? I don't have much else to add at this point, but maybe after posting some more server and table (parent and child) details someone will have an answer for you.

No, this is exactly what I was missing. I had forgotten the default index order is useless for a descending lookup like this: I made the change and the performance is 3000x better (the plan's using the index now). Thanks for all your help.