Question 89319 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres and slow forward index scan? – Postgresql

Could anyone throw any ideas, why on Postgres following simple query is 600x slower doing forward index scan than a backward scan (usually this is other way around):

SELECT "events".* FROM "events" ORDER BY "events"."id" ASC LIMIT 1

vs

SELECT "events".* FROM "events" ORDER BY "events"."id" DESC LIMIT 1

Id is a primary key column. Both queries produce exact same plan with exact same estimates. Only difference is backwards scan.

Other one takes 30ms and the other 0.05ms execution time.

—

EDIT: primary key type is bigint

—

EDIT: add query plans

-> Index Scan using events_pkey on events (cost=0.57..747008554.20 rows=200288464 width=1153) (actual time=29.662..29.6...
Planning Time: 0.090 ms
Execution Time: 29.694 ms```

—

```Limit (cost=0.57..4.30 rows=1 width=1153) (actual time=0.016..0.017 rows= 1 loops=1)
-> Index Scan Backward using events_pkey on events (cost=0.57..747008554.20 rows=200288464 width=1153) (actual time=0....
Planning Time: 0.089 ms
Execution Time: 0.049 ms

—

EDIT: Query times stays the same regardless of cold or warm query. ANALYZE or VACUUM don’t have any effect.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem was solved by doing a full vacuum. This was new for me. I don't know the usage patterns of the table as I'm just admin of db's but I guess there has been a lot of modifications recently to that table?

    Previously regular autovacuum has been enough.


  2. 0

    On the top of my head, I’d ask:

    • What is the type of the id column?
    • what is the insertion pattern ? (batches of sequential ids?)

    I’m asking this because UUIDs or other random values will have a higher chance to hit different leaves of the b-tree, and thus tend to reduce cache hits when traversing them sequentially.

    Indexes can be created with an order by clause.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search