Optimise query for fetching data across multiple days in a partitioned table

Question

I'm trying to optimise a query to retrieve records across multiple days on a partitioned table (say: entries). The table is partitioned on a timestamp column (let's say: created_at) and for each day a new table is created due to partition.

The schema of the table

Partitioned table "public.entries"
        Column         |            Type             | Collation | Nullable |                 
-----------------------+-----------------------------+-----------+----------+-
 id                    | character varying(48)       |           | not null |
 person_id             | bigint                      |           | not null |
 created_at            | timestamp without time zone |           | not null |
 created_at_date       | date                        |           |          |
 created_at_time       | time without time zone      |           |          |
Partition key: RANGE (created_at)
Indexes:
    "entries_pkey" PRIMARY KEY, btree (id, created_at)
    "person_id_created_at_key" UNIQUE CONSTRAINT, btree (person_id, created_at)
    "btree_gist_created_at" gist (person_id, tsrange(created_at, created_at, '[]'::text))
    "person_id_cd_ct_idx" btree (person_id, created_at_date, created_at_time)
    "person_id_created_at_idx" btree (person_id, (created_at::date), (created_at::time without time zone))
Number of partitions: 30 (Use \d+ to list them.)

I need to fetch all the possible results for a particular person who has done entries between a certain period of days (say: 2022-06-01 to 2022-06-05, from 08:00 to 20:00). Each partitioned table has around 3 million rows, meaning we have ~3 million daily entries. As soon as I increase the range of the period of days, my query time increases, which becomes a problem for me when I need to cater for getting results for 30 days.

I also have tried adding different types of indexes on person_id & created_at but still, I'm unable to query much quicker than I need it to be. I need to get the results in a single-digit ms (~5ms - ~9ms) (if that is even possible). Currently, for the mentioned queries, the times I'm getting are ~20ms to ~100ms considering all of them, but I want it to be in single-digits.

The following queries are taking more than ~100ms if I try to query over 30 days of records. I need to optimise these queries for 30 days, though the queries mentioned in the example are for 5-6 days only for the sake of it.

I'm trying to use the following queries to fetch the results:

-- QUERY #1 (Most efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    (
    (
        created_at >= '2022-06-01 08:00:00'
        AND created_at <= '2022-06-01 20:00:00'
    ) OR (
        created_at >= '2022-06-02 08:00:00'
        AND created_at <= '2022-06-02 20:00:00'
    ) OR (
        created_at >= '2022-06-03 08:00:00'
        AND created_at <= '2022-06-03 20:00:00'
    ) OR (
        created_at >= '2022-06-04 08:00:00'
        AND created_at <= '2022-06-04 20:00:00'
    ) OR (
        created_at >= '2022-06-05 08:00:00'
        AND created_at <= '2022-06-05 20:00:00'
    )
);

-- QUERY #2 (2nd most efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    created_at_date >= '2022-06-01'
AND    created_at_date <= '2022-06-05'
AND    created_at_time >= '08:00:00'
AND    created_at_time <= '20:00:00';

-- QUERY #3 (Least efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    (
    '[2022-06-01 08:00:00, 2022-06-01 20:00:00]'::tsrange @> created_at
    OR '[2022-06-02 08:00:00, 2022-06-02 20:00:00]'::tsrange @> created_at
    OR '[2022-06-03 08:00:00, 2022-06-03 20:00:00]'::tsrange @> created_at
    OR '[2022-06-04 08:00:00, 2022-06-04 20:00:00]'::tsrange @> created_at
    OR '[2022-06-05 08:00:00, 2022-06-05 20:00:00]'::tsrange @> created_at
);

I'm sharing the query plans for the above-mentioned queries:

• Query #1
• Query #2
• Query #3

Can you suggest to me if is there any way I can make the query more efficient either by changing the structure of the table or by using some different sort of index combinations? Is there any other possibility or the way I'm querying my DB is the most efficient query I can do depending on my scenario?

Answer 1

Your standards are very high here and might not be attainable.

As your date range increases, you get less and less benefit from partitioning by date. Indeed, it might already be counterproductive, as each partition visited means another index which needs to be visited, and so another opportunity for cache misses. This is especially true since person_id seems to be extremely selective, so all the entries for one value over the entire table might fit into just one or a few index pages, but when considering the partitioning it would instead by a small fraction of several different pages.

If you absolutely need to partition by time, maybe do it by week or month, rather than daily.

Your plan timings seem rather erratic at the node level. I suspect the time is just driven by whether a particular page or handful of pages happen to have been found in the cache, or needed a true disk read. So faster IO or more RAM for caching, or just prewarming the data if you already have enough RAM it just isn't populated, might be the easiest way to speed this up.

Your 2nd query can't benefit from partitioning (but still needs to pay the price for fragmented indexes) because the planner doesn't know the relationship between created_at and created_at_date, so can't prune partitions. You could easily fix that part by changing that fragment to use created_at:

AND created_at >= '2022-06-01'
AND created_at < '2022-06-06'

Answer 2

I did not retest with Postgres 14 or 15, but with earlier versions, the fastest possible way for this kind of query is to split it up into separate queries on partitions directly and UNION ALL.

SELECT *
FROM   entries_p2022_06_01
WHERE  person_id = '111111'
AND    created_at >= '2022-06-01 08:00:00'
AND    created_at <  '2022-06-01 20:00:00'

UNION ALL
SELECT *
FROM   entries_p2022_06_02
WHERE  person_id = '111111'
AND    created_at >= '2022-06-02 08:00:00'
AND    created_at <  '2022-06-02 20:00:00'

-- etc.

The SQL string is easy to generate with a simple function taking person_id, date and time range. (I would consider a function that builds and executes the query directly and returns results.)

Note < in created_at < '2022-06-01 20:00:00'. <= is typically incorrect in such ranges.

This circumvents partitioning logic completely and goes to partitions (tables) directly. It also removes the "ugly OR" from the query, which does not seem to cost much in your fastest plan, but the cost will rise with more time slots like you are planning (30 instead of 5). See:

• Why is an OR statement slower than UNION?

The only index you need for that is on (person_id, created_at). You already have that, close to perfect.

But if your paramount objective is to optimize read performance for said query, and since the size of cache memory seems to be a limiting factor, you can do more. Your table definition reveals:

        Column         |            Type             | Collation | Nullable |                 
-----------------------+-----------------------------+-----------+----------+-
 id                    | character varying(48)       |           | not null |
 person_id             | bigint                      |           | not null |
 created_at            | timestamp without time zone |           | not null |
 created_at_date       | date                        |           |          |
 created_at_time       | time without time zone      |           |          |

You currently have Range Partitioning on created_at, with daily partitions. That's not very good for the task at hand.

Either form bigger partitions - I suggest per month. And drop the completely redundant columns created_at_date and created_at_time. Size matters. Those can be generated with created_at::date and created_at::time cheaply.

Or, if you want to keep daily partitions (and do not work with time ranges across days?) use List Partitioning on created_at_date instead, and drop the column created_at. It can be generated from created_at_date + created_at_time cheaply.

person_id sounds like a reference to human beings. Do you anticipate to involve more than two billion distinct persons over the lifetime of your database? Or are you burning many IDs? Because, if not, a plain integer should do - with its range -2147483648 to 2147483648. 4 bytes instead of 8 bytes for bigint, in table and indexes.

Then your UNIQUE constraint can be replaced with one on (person_id, created_at_time) per partition - with an optimized, combined payload size of 8 bytes for the index tuple. See:

• Is a composite index also good for queries on the first field?

And your query is reduced to:

SELECT *
FROM   entries_p2022_06_01
WHERE  person_id = '111111'
AND    created_at_time >= '08:00'
AND    created_at_time <  '20:00'
...

Removing the redundant columns (and indexes based on them) also means you focus your limited cache memory on fewer and smaller indexes (and a smaller main table on top of that).

Aside

id character varying(48) and PRIMARY KEY (id, created_at) also look suspicious.