How to keep postgres statistics up to date to encourage the best index to be selected

user_10583 0
Viewed 44

I have a Notifications table with approximately 7,000,000 records where the relevant columns are:

id: integer
time_created: timestamp with time zone
device_id: integer (foreign key to another table)

And the indexes:

CREATE INDEX notifications_device ON notifications (device_id);
CREATE INDEX notifications_time ON notifications (time_created);

And my query:

SELECT COUNT(*) AS "__count"
FROM "notifications"
WHERE ("notifications"."device_id" IN (
    SELECT "id" FROM device WHERE (
        device."device_type" = 'iOS' AND
        device."registration_id" IN (
             'XXXXXXX',
             'YYYYYYY',
             'ZZZZZZZ'
        )
    )
)
AND "notifications"."time_created" BETWEEN
    '2020-10-26 00:00:00' AND '2020-10-26 17:33:00')
;

For most of the day, this query will use the index on device_id, and will run in under 1ms. But once the table is written to very quickly (logging notifications sent) the planner switches to using the index on time_created and the query blows out to 300ms.

Running an ANALYZE NOTIFICATIONS immediately fixes the problem, and the index on device_id is used again.

The table is pruned to the last 30 days each night, which is why there is a separate index on the time_created column.

Can I fix this issue, so that the planner always chooses the index on device_id, by forcing postgres to maintain better statistics on this table? Alternatively, can I re-write the time_created index (perhaps by using a different index type like BRIN) so that it'd only be considered for a WHERE clause like time_created < ..30 days ago.. and not WHERE time_created BETWEEN midnight and now?

EXPLAIN ANALYZE stats:

Bad Plan (time_created):

Rows Removed by Filter      = 20926
Shared Hit Blocks           = 143934
Plan Rows                   = 38338
Actual Rows                 = 84479

Good Plan (device_id):

Rows Removed by Filter      = 95
Shared Hit Blocks           = 34
Plan Rows                   = 1
Actual Rows                 = 0
postgresql
2 Answers

I would actually suggest a composite index on the notifications table:

CREATE INDEX idx1 ON notifications (device_id, time_created);

This index would cover both restrictions in the current WHERE clause. I would also add an index on the device table:

CREATE INDEX idx2 ON device (device_type, registration_id, id);

The first two columns of this 3-column index would cover the WHERE clause of the subquery. It also includes the id column to completely cover the SELECT clause. If used, Postgres could more rapidly evaluate the subquery on the device table.

You could also play around with some slight variants of the above two indices, by changing column order. For example, you could also try:

CREATE INDEX idx1 ON notifications (time_created, device_id);
CREATE INDEX idx2 ON device (registration_id , device_type, id);
user_18632290

The table is pruned to the last 30 days each night, which is why there is a separate index on the time_created column.

But, is that a good reason to have the index? Does it matter if the nightly query takes a little longer? Indeed, for deleting 3% of a table, does it even use the index and if it does, does that actually make it faster? Maybe you could replace the index with partitioning, or with nothing.

In any case, you can use this ugly hack to force it not to use the index:

AND "notifications"."time_created" + interval '0 seconds' BETWEEN '2020-10-26 00:00:00' AND '2020-10-26 17:33:00'
user_17212390
Related