Slow index scan

Question

I have table with index:

Table: Participates (player_id integer, other...) Indexes: "index_participates_on_player_id" btree (player_id)

Table contains 400kk rows. I execute the same query two times:

Query: explain analyze select * from participates where player_id=149294217;

First time:

 Index Scan using index_participates_on_player_id on participates (cost=0.57..19452.86 rows=6304 width=58) (actual time=261.061..2025.559 rows=332 loops=1) Index Cond: (player_id = 149294217) Total runtime: 2025.644 ms (3 rows)

Second time:

 Index Scan using index_participates_on_player_id on participates (cost=0.57..19452.86 rows=6304 width=58) (actual time=0.030..0.479 rows=332 loops=1) Index Cond: (player_id = 149294217) Total runtime: 0.527 ms (3 rows)

So, first query has big actual time - how to increase speed the first execute?

UPDATE Sorry, How to accelerate first query?)

Why index scan search so slow?

khampson · Accepted Answer · 2014-05-26 16:00:10Z

The difference in execution time is probably because the second time through, the table/index data from the first run of the query is in the shared buffers cache, and so the subsequent run of the query takes less time, since it doesn't have to go long-path to disk for that information.

Edit:

Regarding the slowness of the original query, does the table have a lot of dead tuples? Those can slow things down considerably. If so, VACUUM ANALYZE the table.

Another factor can be if there are long-ish idle transactions on the server (i.e. several minutes or more). Due to the nature of MVCC this can also slow even index-based queries down quite a bit.

Also, what the query planner is expecting for the results vs. actual is quite different, so you may want to do an ANALYZE on the query beforehand to update the stats.

double_word_disruptor · Accepted Answer · 2014-05-26 17:14:06Z

1.) Take a look at http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html and check out for some tuning for using more memory. This can speed up your search but will not give you a warranty (depending on the answer before)!

2.) Transfer a part of your tables/indexes to a more powerful tablespace. For example a tablespace based on SSDs.

Collectives™ on Stack Overflow

Slow index scan

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related