Postgresql – How to stabilize performance on frequently updated table in PostgreSQL

You have two "ranges" filtering the data. MySQL has no good way to do that.

First, let's check some numbers.

• How many rows in the result set?
• How many rows without the IN clause?
• How many rows without the time range?
• Is this IN list and this time range typical?

With those, we may be able to decide to focus on filtering on the time range in preference over the IN range.

Meanwhile, let's get rid of the "midnight" bug... Your time range seems to be from the morning of May 1 through midnight of the morning of Oct 31. Perhaps you meant to include all of Oct 31, not just one millisecond of that day? Did you mean to ask for a full 6 months? Suggest this coding style; it's less error-prone:

AND data_received_time >= 1000 *  UNIX_TIMESTAMP('2015-05-01')
AND data_received_time <  1000 * (UNIX_TIMESTAMP('2015-05-01') + INTERVAL 6 MONTH)

Back to the performance question...

If you are looking at a smallish fraction of the total time, then PARTITIONing may be useful for simulating a 2D index. (I mention this as Case #2 in http://mysql.rjweb.org/doc.php/partitionmaint )

If you break the table into, say, 20-40 partitions PARTITION BY RANGE(data_received_time), then "partition pruning" will first filter on the time range. Then your PRIMARY KEY, with imei_no first, filters on the other dimension.

I cannot say how much (if any) this partitioning will speed things up. I would expect small time ranges to benefit (percentage-wise) the most. A time range that hits all partitions will actually be a little slower than without partitioning. (Hence, my "check some numbers" questions.)

Schema notes... (Smaller is faster)

• BIGINT is 8 bytes. Can odom go beyond 16M miles, the limit for `MEDIUMINT UNSIGNED (3 bytes)?

• INT(4) -- the (n) means nothing; suggest TINYINT (1 byte) or SMALLINT (2 bytes).

• Some of the VARCHARs could be 'normalized' to say a 2-byte SMALLINT UNSIGNED.

The thing about indexes are that they are excellent tools for speeding up queries, but they bring along overhead as well.

1. If you choose to index every column in a table that is involved in a query, you have chosen to store considerably more data. This has an effect on the storage required and in the overhead of maintaining all those indexes. (Or maintaining all columns in a very wide index.)
2. In past experience, having many indexes, especially several indexes with similar distribution statistics, can make it easier for the server to select a poor index and thus a poor plan.

My experience shows that indexing just about everything will hurt your performance overall, for the reasons mentioned above.

Both table A and table B should only contain the indexes that would be of best use for your queries.

Of course, your first indexing decisions may need to be refined over time as you learn more about your database's behavior. Do not be afraid to add additional indexes when needed. And do not be afraid to drop an index that is not being useful.