0

I have the following table with 462541359 rows.

create table "Prices"
(
    "Id"            bigint generated by default as identity
        constraint "PK_Prices"
            primary key,
    "Timestamp"     timestamp with time zone not null,
    "DieselPrice"   real                     not null,
    "E5Price"       real                     not null,
    "E10Price"      real                     not null,
    "DieselChanged" boolean                  not null,
    "E5Changed"     boolean                  not null,
    "E10Changed"    boolean                  not null,
    "StationId"     uuid                     not null
        constraint "FK_Prices_Stations_StationId"
            references "Stations"
            on delete cascade
);

alter table "Prices"
    owner to postgres;

create index "IX_Prices_DieselChanged"
    on "Prices" ("DieselChanged");

create index "IX_Prices_E10Changed"
    on "Prices" ("E10Changed");

create index "IX_Prices_E5Changed"
    on "Prices" ("E5Changed");

create index "IX_Prices_StationId"
    on "Prices" ("StationId");

create index "IX_Prices_Timestamp"
    on "Prices" ("Timestamp");

I stripped my query down to this (as a minimal example)

select
    count(*)
FROM "Prices"
where "StationId" = 'f38e56c1-e9ba-428f-adb0-bdefa428559b'
  and "Timestamp" >= '2023-01-07'

where StationId is just one of 17000 station ids.

When I filter this table I get poor performance (approx. 8s) for the initial run. When I re-run the query it's way faster (approx. 300ms). When I change the StationId the first query is slow again.

I tried to analyze the performance by using EXPLAIN (ANALYZE, BUFFERS) and got the following result

Aggregate  (cost=124159.66..124159.67 rows=1 width=8) (actual time=7734.619..7734.620 rows=1 loops=1)
  Buffers: shared read=38398
  ->  Bitmap Heap Scan on ""Prices""  (cost=358.69..124141.54 rows=7246 width=0) (actual time=6668.499..7732.704 rows=9678 loops=1)
        Recheck Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
        Filter: (""Timestamp"" >= '2023-01-07 00:00:00+00'::timestamp with time zone)
        Rows Removed by Filter: 28645
        Heap Blocks: exact=38323
        Buffers: shared read=38398
        ->  Bitmap Index Scan on ""IX_Prices_StationId""  (cost=0.00..356.88 rows=33107 width=0) (actual time=21.983..21.983 rows=38364 loops=1)"
              Index Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
              Buffers: shared read=34
Planning Time: 0.082 ms
JIT:
  Functions: 7
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.296 ms, Inlining 0.000 ms, Optimization 0.196 ms, Emission 2.469 ms, Total 2.961 ms
Execution Time: 7734.984 ms

From what I've read the Bitmap Heap Scan is only partially using the index on the Timestamp column and I suspect this to be the cause of the low performance.

What can be the cause for the initial slowness of the query and how can I utilize indizes better to speed up the filtering by a date? And how can I ensure that I keep the performance of the index when I combine my filter with more filters like E5Changed?

1
  • 1
    Have you considered using composite indexes? By the way, plain indexes on boolean columns are rarely useful.
    – mustaccio
    Jan 9 at 12:58

1 Answer 1

0

Here's the explain with nice formatting on depesz.

So the bitmap scan uses the index on StationId to flag 38364 rows. This reads almost the same number of buffers which means the data was probably inserted in timestamp order, spreading rows with any individual StationId all over the table, which is usually the case with time series data.

This large number of random reads explains why the query is slow, especially if you are not using SSDs.

Then 75% of these rows do not satisfy the condition on timestamp, so only 25% of the rows are kept.

Now, why doesn't it use the index on timestamp? Assuming the timestamps are distributed in the same manner for all StationId's, this means your timestamp condition would hit 25% of rows. If it did a BitmapAnd to combine indices on Timestamp and StationId, then it would have to scan all the index rows satisfying the condition in both the StationId index and the Timestamp index, and combine the two in a bitmap. In the Timestamp index, this would be 25% of 462541359, or about 115M index rows. Postgres makes the reasonable assumption this won't be the fastest option, so it chooses another plan, which is what you got.

A much better option would be a multicolumn index on (StationId,Timestamp) or (Timestamp,StationId), which would satisfy the conditions directly with an index scan.

But... which one should you choose? An index on (a,b) is also an index on (a) but not an index on (b). So, the chosen multicolumn index will replace one of the existing indices, either on StationId or Timestamp.

An index on (a,b) allows range searches on (a) and (a,b) but not on (b) alone. Just like a phone book sorted on (last name, first name) optimizes searches like "last name is 'Smith' and first_name starts with 'A' or 'B'" because all the rows satisfying that are clustered as a range inside the index.

Since StationId is an uuid, you'll never make range queries on it. But you will probably make queries combining "SiationId=constant" and "Timestamp in a range", using "<" or BETWEEN. So it makes a lot more sense to create an index on (StationId,Timestamp) which will optimize these.

alter table "Prices" owner to postgres;

It's better to use a normal user instead of postgres, for security reasons.

create index "IX_Prices_DieselChanged" on "Prices" ("DieselChanged");
create index "IX_Prices_E10Changed" on "Prices" ("E10Changed");
create index "IX_Prices_E5Changed" on "Prices" ("E5Changed");

Non-selective indices will never be used, so they are a waste of resources.

If the statistical distribution of the bool column isn't something like 99% for one value and 1% for the other, the index is useless. Unlike an index on timestamp which can be used to pick one timestamp out of millions in the table, bool has only two possible values...

If your table has only a few percent of rows with the bool column set to true, then that may be selective enough. But in this case, the vast majority of the rows in the index have the bool set to false, and are wasting space. In this case it is better to create a conditional index on (bool_column) WHERE bool_column. So the index will only store the rows with the value "true". (Or WHERE not bool_column, if that's more selective). But usually single column indices on bool aren't useful.

Even if the bool column is very selective, it would probably be more efficient to create a conditional index on (StationId,Timestamp) WHERE bool_column if you often search on these in your queries. This will also work as a plain index like (bool_column) WHERE bool_column.

If your table gets huge, you can also partition it by timestamp (say, by month). Then, you can use CLUSTER on old partitions to reorder the rows on disk in (StationId,Timestamp) order which will make the distribution of rows in the table a lot more friendly to the query in the question. However, since the rows are no longer sorted by timestamp, it will have the opposite effect on queries using only conditions on timestamp and not StationId.

Or you can use a database specialized in time series, like clickhouse:

select topic, count(*) from mqtt_float group by topic order by 2 desc limit 3;

┌─topic──────────────────────────────────────┬───count()─┐
│ pv/meter/total_power                       │ 232506715 │
│ pv/meter/phase_1_current                   │  50604345 │
│ pv/meter/phase_2_current                   │  49684640 │
└────────────────────────────────────────────┴───────────┘

5 rows in set. Elapsed: 0.979 sec. Processed 1.58 billion rows, 1.58 GB (1.61 billion rows/s., 1.61 GB/s.)

select count(*) from mqtt_float where topic='pv/meter/phase_1_current' and ts > '2023-10-01';

┌──count()─┐
│ 13054580 │
└──────────┘

1 row in set. Elapsed: 0.019 sec. Processed 13.12 million rows, 118.07 MB (685.46 million rows/s., 6.17 GB/s.)

This has another completely different set of compromises and use cases, of course. For example, it doesn't do updates, only inserts. There are several databases specialized in time series, all with their own quirks and sets of compromises too.

4
  • thanks a lot, your answer is very helpful. I have a few questions: 1. You talked about index order (a,b) or (b,a) and said I should use (StationId,Timestamp) but shouldn't I use (Timestamp, StationId) as I want range queries on Timestamp and that's only possible on a? 2. For my actual use case I also want to filter by E5Changed, E10Changed etc. For this to utilize indizes would I have to include this in the composite index? 3. My data is 33GB and I have 63Gb of indizes (partially cause I was testing). Is there a certain timeseries DB you would recommend? Jan 9 at 11:40
  • 1) if you often do "where StationId=constant AND range of timestamp" then the index on (StationId,Timestamp) is best, it should replace the index on StationId. If you also do ranges on timestamp without adding StationId conditions, then keep the current index on Timestamp, it will be used. 2) What is the statistical distribution of the bool columns (% rows true / % rows false)?
    – bobflux
    Jan 9 at 11:44
  • 2) Approx. 78% true and 22% false Jan 9 at 11:57
  • I don't think an index would be useful for these, not selective enough
    – bobflux
    Jan 9 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.