Android Paging3 Room performance

Question

I'm playing with Paging3 with Room. I wrote a little test app that populates a Room DB with a single table with 5 string fields (plus its id). I populate the table with 100,000 items.

The code for test project is here: https://github.com/johngray1965/wordlePeople

The list is a recyclerview with a PagingDataAdapter.

There are options in the UI to select filters for the color and gender.

The initial loading works fine. Filtering down the data works fine. Clearing the filter (going back to full list) sends the app into a dizzy for a number of seconds. I've looked at the profiling data in Android Studio (which I'm finding to be a source of frustration). I can see where I did the touch for the clear, I can see that resulted in garbage collection. Then the UI seems to be moderately busy for a long time (the UI is unresponsive for 5-7 seconds when not running the profiler).

It doesn't look like the query is what's slow. But I don't see the problem if there are only 2,000 items in the table.

I've turned the debug logging in Room, and I can see the queries always get all the items in a subquery, and then apply the limit and offset:

SELECT * FROM ( SELECT * FROM wordle_people ) LIMIT 90 OFFSET 0

BTW, I see the problem without debug logging in Room, on an emulator, or on a real device.

Here's the entity:

@Entity(
    tableName = "wordle_people",
    indices = [
        Index(value = ["gender"], unique = false),
        Index(value = ["color"], unique = false),
    ]
)
data class WordlePerson(
    @PrimaryKey(autoGenerate = true)
    override var id: Long? = null,
    var firstName: String = "",
    var middleName: String = "",
    var lastName: String = "",
    @TypeConverters(Gender::class)
    var gender: Gender,
    @TypeConverters(Color::class)
    var color: Color
): BaseEntity()

The Dao:

@Dao
abstract class WordlePersonDao : BaseDao<WordlePerson>("wordle_people") {
    @Query("SELECT * FROM wordle_people")
    abstract fun pagingSource(): PagingSource<Int, WordlePerson>

    @Query("SELECT * FROM wordle_people where gender IN (:genderFilter)")
    abstract fun pagingSourceFilterGender(genderFilter: List<Gender>): PagingSource<Int, WordlePerson>

    @Query("SELECT * FROM wordle_people where color IN (:colorFilter)")
    abstract fun pagingSourceFilterColor(colorFilter: List<Color>): PagingSource<Int, WordlePerson>

    @Query("SELECT * FROM wordle_people where color IN (:colorFilter) AND gender IN (:genderFilter)")
    abstract fun pagingSourceFilterGenderAndColor(genderFilter: List<Gender>, colorFilter: List<Color>): PagingSource<Int, WordlePerson>

}

Relevant parts of the ViewModel:

    private val stateFlow = MutableStateFlow(FilterState(mutableSetOf(), mutableSetOf()))

    @OptIn(ExperimentalCoroutinesApi::class)
    val wordlePeopleFlow = stateFlow.flatMapLatest {
        Pager(
            config = PagingConfig(
                pageSize = 30,
            ),
            pagingSourceFactory = {
                when {
                    it.colorSet.isEmpty() && it.genderSet.isEmpty() ->
                        wordlePeopleDao.pagingSource()
                    it.colorSet.isNotEmpty() && it.genderSet.isNotEmpty() ->
                        wordlePeopleDao.pagingSourceFilterGenderAndColor(it.genderSet.toList(), it.colorSet.toList())
                    it.colorSet.isNotEmpty() ->
                        wordlePeopleDao.pagingSourceFilterColor(it.colorSet.toList())
                    else ->
                        wordlePeopleDao.pagingSourceFilterGender(it.genderSet.toList())
                }

            }
        ).flow
    }

And finally in the fragment, we get the data and pass to the adapter:

        lifecycleScope.launchWhenCreated {
            viewModel.wordlePeopleFlow.collectLatest { pagingData ->
                adapter.submitData(pagingData)
            }
        }

Answer 1

The smaller red circle is the db lookup, the larger red circle is all the DiffUtil. Its selected, you can see FlameChart for it. I might be wrong, but this looks like a bug in the PagingDataAdapter