Lossy Mode

A query that results in a large number of rows may put you in lossy mode.

Every database connection has allocated space. The amount is set by WORK_MEM in your postgres settings. If the bitmap cannot reference each tuple in the relation within the allocation then the database planner will enter lossy_mode.

You can determine if you are in lossy_mode by checking the query plan.

Here are a few common approaches to reduce the bitmap heap scan size. Typically, I would recommend them in the order listed:

  1. Improve your query
  2. Paginate you results
  3. Optimize your indexes
  4. Add a partial index
  5. Increase the WORK_MEM

Example from the query plan:

EXPLAIN ANALYZE SELECT * FROM large_table

=> ....
    Heap Blocks: exact=2000 lossy=50000
   ...

 

Did you like this article? Check out these too.

 

Found this useful? Have a suggestion? Get in touch at blog@hocnest.com.