1

I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used.

Index not used:

explain (buffers, analyze) select * from my_table where column_a in (1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1306095.85 rows=7772384 width=648) (actual time=0.009..6231.312 rows=7720995 loops=1)
   Filter: (column_a = 1)
   Rows Removed by Filter: 7398657
   Buffers: shared hit=463612 read=653528
 Planning time: 0.840 ms
 Execution time: 7717.923 ms
(6 rows)

Index Used:

explain (buffers, analyze) select * from my_table where column_a in (8);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=1378.58..233236.51 rows=73567 width=648) (actual time=14.258..67.795 rows=74756 loops=1)
   Recheck Cond: (column_a = 8)
   Heap Blocks: exact=36425
   Buffers: shared hit=36632
   ->  Bitmap Index Scan on my_table_column_a_idx  (cost=0.00..1360.19 rows=73567 width=0) (actual time=8.595..8.596 rows=74756 loops=1)
         Index Cond: (column_a = 8)
         Buffers: shared hit=207
 Planning time: 0.855 ms
 Execution time: 82.253 ms
(9 rows)

I have the table vacuumed:

select relallvisible, relpages, relallvisible/relpages as ratio from pg_class where relname='my_table';
 relallvisible | relpages | ratio
---------------+----------+-------
       1117140 |  1117140 |     1
(1 row)
2

That is just as it should be.

If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway.

So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.

  • The issue I'm seeing is that sometimes our web application makes over 200 requests with similar queries and postgresql CPU overloads and it crashes... – Anthony Aug 23 at 13:09
  • The web application doesn't want to receive 8 million result rows, so set a LIMIT on the number of rows returned. Then the index will be used, and the query will be fast. You should use a connection pool so that there cannot be 200 concurrent queries. As you have seen, nobody benefits if that happens. – Laurenz Albe Aug 23 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.