Why does it do an index scan when there are indexes on all the relevant columns?

Question

I have an index on car_id and an index on ended_at.

Is this query taking so long because I am ordering it by id and I have separate unique indexes?

Would it be better if I ordered it by ended_at and then made an index on both ended_at and car_id?

SELECT  "trip_reports".*
FROM "trip_reports"
WHERE "trip_reports"."car_id" = $1 AND (ended_at < '2020-11-03 17:31:09')
ORDER BY "trip_reports"."id" DESC
LIMIT $2

Duration is 6.05 minutes.

The query plan:

Limit (cost=0.56..4512.17 rows=1 width=1156)
  -> Index Scan Backward using trip_reports_pkey on trip_reports (cost=0.56..9830786.80 rows=2179 width=1156)
     Filter: ((ended_at < '2020-11-03 20:55:57'::timestamp without time zone) AND (car_id = 103638))

EXPLAIN (ANALYZE, BUFFERS)

Limit  (cost=0.56..4512.67 rows=1 width=1156) (actual time=976974.363..976974.363 rows=0 loops=1)
  Buffers: shared hit=2071575 read=3222036
  ->  Index Scan Backward using trip_reports_pkey on trip_reports  (cost=0.56..9831877.02 rows=2179 width=1156) (actual time=976974.361..976974.361 rows=0 loops=1)
        Filter: ((ended_at < '2020-11-03 17:31:09'::timestamp without time zone) AND (car_id = 119780))
        Rows Removed by Filter: 22862225
        Buffers: shared hit=2071575 read=3222036
Planning time: 0.113 ms
Execution time: 976975.711 ms

We'd need the result of EXPLAIN (ANALYZE, BUFFERS) for the query to give an answer. — Laurenz Albe
– Laurenz Albe, Commented Nov 4, 2020 at 14:13
Try a multi-column index. (Your query will only use one index.) — jarlh
– jarlh, Commented Nov 4, 2020 at 14:32
@jarlh Ok, I will try. Is this because it has an Order By in the query? — stevo999999
– stevo999999, Commented Nov 4, 2020 at 14:45

Laurenz Albe · Accepted Answer · 2020-11-04 15:26:54Z

2

In my opinion:

Try creating an index for the WHERE condition:
```
create index trip_reports_carid_endtime on trip_reports (car_id, ended_at);
```
(column order in index is important)
If you did not do it before:
```
vacuum (analyse) trip_reports;
```

edited Nov 4, 2020 at 15:26

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Nov 4, 2020 at 14:56

areklipno

5285 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stevo999999 Over a year ago

how can I vacuum asynchronously?

areklipno Over a year ago

postgres use few "workers" to do vacuum - number of max workers is parameter max_parallel_maintenance_workers

Collectives™ on Stack Overflow

Why does it do an index scan when there are indexes on all the relevant columns?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related