2

I wanted to get any ideas/information on how to attack the following performance issue.

Given the following query:

select *
from station_status_history
where station_id = <...>
order by timestamp desc
limit 1;

It will perform rather different for 2 different station_id values:

  • station 1: query will be fast. there are roughly 6000k records for this station id. Latest records are no older than a few minutes ago from now
  • station 2: query will be very slow. there is only 1 record in the table and it is generally quite old (e.g. even up to one year old)

Here are the to query plans for each station:

The fast one:

Limit  (cost=0.56..644.44 rows=1 width=134) (actual time=0.105..0.106 rows=1 loops=1)
  Buffers: shared hit=15 read=1 dirtied=1
  ->  Index Scan Backward using station_status_history_pk on station_status_history  (cost=0.56..3726099.44 rows=5787 width=134) (actual time=0.104..0.104 rows=1 loops=1)
        Index Cond: (station_id = 17453)
        Buffers: shared hit=15 read=1 dirtied=1                <========= 
Total runtime: 0.128 ms

The slow one:

Limit  (cost=0.56..644.44 rows=1 width=134) (actual time=5040.417..5040.418 rows=1 loops=1)
  Buffers: shared hit=12804 read=730743 written=1060
  ->  Index Scan Backward using station_status_history_pk on station_status_history  (cost=0.56..3726099.44 rows=5787 width=134) (actual time=5040.415..5040.415 rows=1 loops=1)
        Index Cond: (station_id = 16799)
        Buffers: shared hit=12804 read=730743 written=1060     <========= 
Total runtime: 5040.467 ms

My suspicion was the records's age and its relation with the Buffers information but didn't know how to go about it to fix it

PG version:

PostgreSQL 9.3.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit

Table size and description

  • records: 42 million
  • total size: 16 GB
# \d+ station_status_history;
                                     Table "public.station_status_history"
             Column              |           Type           | Modifiers | Storage | Stats target | Description
---------------------------------+--------------------------+-----------+---------+--------------+-------------
 timestamp                       | timestamp with time zone | not null  | plain   |              |
 station_id                      | integer                  | not null  | plain   |                          |
 is_resampled                    | boolean                  | not null  | plain   |              |
 weight                          | integer                  |           | plain   |              |
 ...
Indexes:
    "station_status_history_pk" PRIMARY KEY, btree ("timestamp", station_id, is_resampled)
    "ix_station_status_history_id" UNIQUE, btree (id)
Foreign-key constraints:
    "station_status_history_station_id_fkey" FOREIGN KEY (station_id) REFERENCES station(id) ON DELETE CASCADE
    "station_status_history_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
Referenced by:
    TABLE "operation" CONSTRAINT "operation_station_status_history_id_fkey" FOREIGN KEY (station_status_history_id) REFERENCES station_status_history(id)
    TABLE "task" CONSTRAINT "task_station_status_history_id_fkey" FOREIGN KEY (station_status_history_id) REFERENCES station_status_history(id)
    TABLE "task_update" CONSTRAINT "task_update_station_status_history_id_fkey" FOREIGN KEY (station_status_history_id) REFERENCES station_status_history(id)
Has OIDs: no
Improve this question
2

3 Answers 3

Reset to default
3

The behaviour you describe is expected with the index you have on (timestamp, station_id, ...). The plan is doing a backwards scan on that index and sometimes it finds the first matching row fast and sometimes it has to go through thousands or million of rows before it find the first matching row.

I suggest you add an index on (station_id, timestamp). The plan will change to do just an index seek on this index (and then a lookup on the table to get the data from other columns). You only need one row and there will be no scan.

Improve this answer
0
1

Thanks for the quick answer @ypercube !

I did what you suggested (on a test database hence the smaller numbers on the examples below) and whilst I think the performance issue is fixed, I think it is not doing 100% what you imagined: it is indeed using the new index but the steps in the plan are the same ( same Index Scan Backward as opposed to Index seek like you mentioned)

Here is the plan for the slow station without modifications:

Limit  (cost=0.43..44.53 rows=1 width=134) (actual time=136.025..136.026 rows=1 loops=1)
  Buffers: shared hit=42639
  ->  Index Scan Backward using station_status_history_pk on station_status_history  (cost=0.43..248947.57 rows=5646 width=134) (actual time=136.024..136.024 rows=1 loops=1)
        Index Cond: (station_id = 13543)
        Buffers: shared hit=42639
Total runtime: 136.111 ms

and here is the plan with the new index:

CREATE INDEX station_status_history_new_index_test ON station_status_history (station_id, timestamp);

Limit  (cost=0.43..4.35 rows=1 width=134) (actual time=0.023..0.023 rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Scan Backward using station_status_history_new_index_test on station_status_history  (cost=0.43..22154.13 rows=5649 width=134) (actual time=0.021..0.021 rows=1 loops=1)
        Index Cond: (station_id = 13543)
        Buffers: shared hit=4
Total runtime: 0.071 ms

So yes a lot quicker!

I just wanted to confirm this was what you meant ?

EDIT After reading mustaccio's comment I tried the following index (with desc in the timestamp column) :

CREATE INDEX station_status_history_new_index_test ON station_status_history (station_id, timestamp desc);

and the plan changed slightly, maybe for the better even ?

the plan for the slow station with new index with desc timestamp:

Limit  (cost=0.43..4.35 rows=1 width=134) (actual time=0.022..0.023 rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using station_status_history_new_index_test on station_status_history  (cost=0.43..22242.15 rows=5672 width=134) (actual time=0.021..0.021 rows=1 loops=1)
        Index Cond: (station_id = 13543)
        Buffers: shared hit=4
Total runtime: 0.061 ms

So now we are no longer using Index Scan Backward but Index Scan instead.

Is that generally better or we only need to look at the final execution times?

Was this the actual plan you were expecting to see @ypercube when you first answered the question ?

Improve this answer
5
  • Yes, indeed. I might had been confused with plans from other DBMS. Index scan backwards looks ok. I think this is generic, if the LIMIT was higher than 1, it would need to scan more than one row. Commented Oct 1, 2021 at 12:31
  • 1
    As for choosing between (station_id, timestamp) and (station_id, timestamp DESC), the difference in efficiency would be negligible. As obvious from the plan, Postgres can do backwards scans. Commented Oct 1, 2021 at 12:34
  • The performance of the above two indexes will differ for different queries, e.g. with ORDER BY station_id, timestamp vs. ORDER BY station_id, timestamp DESC. Commented Oct 1, 2021 at 12:37
  • I see. Usual use cases will most likely be interested in results sorted in descending order by timestamp, so I think it makes more sense to create the index like so (station_id, timestamp DESC) Commented Oct 1, 2021 at 12:44
  • Sounds good. Also note (not relevant for this question as timestamp is not nullable) special considerations for asc/desc order by and indexes when the column are nullable: dba.stackexchange.com/questions/112810/… Commented Oct 1, 2021 at 12:54
1

Rather than adding more to your question by adding a non-answer answer, you should edit your question if you want to expand or clarify it.

There is nothing actually called an "index seek", as far as query plans go. But that doesn't mean it isn't happening. The plan outputs are cosmetically the same, but the actual operation behind the "Index Cond: (station_id = 13543)" line is quite different for the two indexes. For the slow one, it scans the entire index backwards, filtering on station_id as (what I call) an "in index filter". For the fast one, it seeks to the end of the part of the index where the station_id = 13543 data resides, then scans just that part of the index backwards. (In both cases, it can stop the backwards scan once it finds one visible row--when I say it scans the entire index, I'm not saying it doesn't get to stop early, just that it needs to start at the logical end, not the end of a certain chunk)

It is a shame that the query plan output doesn't distinguish between these operations. Maybe calling one "Index Filter:" rather than "Index Cond:"?

Improve this answer
1
  • Yes, it would be nice if there was a differentiation. Commented Oct 1, 2021 at 17:00

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.