Re: Dirty reads on index scan,

On Thu, 2023-09-21 at 17:05 +0200, Koen De Groote wrote:
> 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.

That's normal behavior: after the first execution, the data are cached, so the query
becomes much faster.

Dirtying pages happens because the first reader has to set hint bits, which is an extra
chore. You can avoid that if you VACUUM the table before you query it.

Yours,
Laurenz Albe