Why is my query taking so long if my field is correctly indexed? I’m trying to figure out why my query is taking so long.  I have indexed my property_id field but still get awful results when querying it.  I have the following Explain and wonder if anyone can shed some light on it?

production=&gt; explain analyze select * from item_events where property_id = 503;


 QUERY PLAN
------------------
Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=137.932..97996.513 rows=23140 loops=1)
  Recheck Cond: (property_id = 503)
  Heap Blocks: exact=15371
  -&gt;  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=123.178..123.178 rows=23141 loops=1)
        Index Cond: (property_id = 503)
Planning time: 0.154 ms
Execution time: 98141.818 ms
(7 rows)


I have the following index that exists:
“index_item_events_on_property_id_and_captured_at” btree (property_id, captured_at)

And here is explain(analyze, buffers):

production=&gt; explain (analyze, buffers) select * from item_events where property_id = 503;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=559.704..97900.064 rows=23140 loops=1)
   Recheck Cond: (property_id = 503)
   Heap Blocks: exact=15371
   Buffers: shared hit=6523 read=8942
   -&gt;  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=556.513..556.513 rows=23140 loops=1)
         Index Cond: (property_id = 503)
         Buffers: shared hit=1 read=93
 Planning time: 0.150 ms
 Execution time: 97934.782 ms
(9 rows)
If you have a 5400 RPM hard drive, this is almost exactly how much time you would expect it to take to read 8942 blocks if there was no effective read-ahead or read re-ordering (as implemented by the kernel, with some collaboration from PostgreSQL).  You can confirm if the time goes to disk reads by turning track_io_timing on before doing the EXPLAIN (ANALYZE, BUFFERS).

Even with a single 5400 hdd, I've seen it get increased performance, up to 2x, by cranking up effective_io_concurrency to a large value, like 50 or 100.

If you repeat the query, is it faster the second time?  Is your goal to make this work from a hot-cache or a cold-cache?  How much RAM do you have?