Postgresql query slowed with table growth

performancepostgresqlpostgresql-performance

I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt.

Table body_feature:

    Column     |       Type       | Modifiers
    -----------+------------------+-----------
     feature   | text             | not null
     body      | text             | not null
     start     | integer          | not null
     stop      | integer          | not null
Indexes:
    "t_feature_ind" btree (feature)

In the old version of the database, there were ~32 million rows in the database.

Here is a simple query:

explain analyze select * from body_feature where feature='ABC';                   

       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_feature_ind on body_feature  (cost=0.00..49544.92 rows=15986 width=69) (actual time=175.967..261.175 rows=7285 loops=1)
   Index Cond: (feature = 'ABC'::text)
 Total runtime: 261.555 ms

With the recent expansion, number of rows has grown to more than 110 million, and the same query gives:

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on body_feature  (cost=944.33..155543.90 rows=42307 width=73) (actual time=1118.598..204052.203 rows=23132 loops=1)
   Recheck Cond: (feature = 'ABC'::text)
   ->  Bitmap Index Scan on t_feature_ind  (cost=0.00..933.75 rows=42307 width=0) (actual time=952.948..952.948 rows=23132 loops=1)
         Index Cond: (feature = 'ABC'::text)
 Total runtime: 204065.405 ms
(5 rows)

The original machine running the server is quite old and has 8 Gb of RAM, but I have access to a 1Tb RAM machine for testing.

What would you suggest I change in the server configuration to make such simple query revert back to the simple index scan?

I tried extracting feature and body values (which are text) into separate tables, and using simple integer IDs in the body_feature table (which cuts the raw size down significantly), but I still get the same results.

Thank you for any suggestions/help.

Best Answer

You are assuming that the problem is the switch to the bitmap scan. That is very unlikely.

The likely problem is that your data had to be read from disk, either because it has grown too large to fit in cache (in which case more RAM would help), or the cache was cold when you happened to run the test.

The switch to bitmap scan is likely a response to the same thing that caused the problem, the growth in data.

You could cluster the table on the index t_feature_ind, which would make it more efficient to pull out all of the data with any given feature.

Related Question