Dirty reads on index scan,

[Koen De Groote <kdg.dev@xxxxxxxxx>]

I'm researching a query that's slow occasionally, and I'm seeing dirtied reads and am asking for some help in understanding.

The table has the following relevant fields:

- insert_timestamp (timestamp without timezone, nullable, default now())

- hasbeenchecked ( boolean, not null )

- hasbeenverified ( boolean. not null )

I'm doing the following query:

select * from my_table where hasbeenchecked = true and hasbeenverified = true and insert_timestamp <= '2023-09-01 00:00:00.000' limit 1000;

The date is an example, it is the format that is used in the query.

The table has 81M rows. Is 50GB in size. And the index is 34MB

The index is as follows:

btree (insert_timestamp DESC) WHERE hasbeenchecked = true AND hasbeenverified = true

I'm seeing a slow query first, then a fast one, and if I move the date, a slow query again.

What I'm seeing is:

Attempt 1:

Hit: 5171(40MB)

Read: 16571(130MB)

Dirtied: 3940(31MB)

Attempt 2:

Hit: 21745 (170MB)

Read: Nothing

Dirtied: Nothing.

It's slow once, then consistently fast, and then slow again if I move the date around.

And by slow I mean: around 60 seconds. And fast is below 1 second.

My settings:

shared_buffers = 2048MB
effective_cache_size = 6GB
checkpoint_completion_target = 0.5
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 64MB

The data is on an SSD. 4CPU/32GB ram.

I've tried increasing the amount of CPUs, but that doesn't seem to affect the performance.

I'm having trouble identifying what exactly is the culprit here, or if there are multiple. Is the table simply too big? Is the query always going to be problematic and I probably need to look at a fundamentally different way of gathering this data? Is it not enough memory? Something else?

Any help would be appreciated.

I'm using the analysis methods explained here to gather this data: https://github.com/dalibo/pev2

Regards,

Koen De Groote