Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qjg7F-00Bh1B-P0 for pgsql-performance@arkaria.postgresql.org; Fri, 22 Sep 2023 13:22:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qjg7D-003rLk-8y for pgsql-performance@arkaria.postgresql.org; Fri, 22 Sep 2023 13:22:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qjg7C-003rLN-Q9 for pgsql-performance@lists.postgresql.org; Fri, 22 Sep 2023 13:22:38 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qjg79-0061pz-SV for pgsql-performance@postgresql.org; Fri, 22 Sep 2023 13:22:37 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-9ad8bba8125so282543066b.3 for ; Fri, 22 Sep 2023 06:22:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1695388953; x=1695993753; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=0ZUDwhKFgzYJK+uqdrp6neE6oFhWPa0GOt7/vDTdL78=; b=hH2zJwRQ2Pww01WtpYTUtWxYpl/mK3v9SnpZiLf2JRuU2+i0vLp7KjC1GMuxDCE6A0 wc0hZB/darje38ODbUKepxzesf1cbLuvs3Nv/zmlr7GzFJ9GLz6PZhzjMztSAQV/cRkl CJRaamcUDyT3UayxcexAh7cNW4Y4RigwSFgs1waESQrQZoD++C6HxwpMpzjlEa/bE8/j lc/cLa/Xl1PNEAzK14tWH5IlF0J062DFh4lWMFfzhx8i0e4sPeIXmPhlYLA5yu1G1kZa ESFMr27hzKp6sVEM7CG1KVEbTrOhK5Nf7DAhx+2J7gTfO+C9QAbwhkJ3ihSB+dSoq5A3 eHQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1695388953; x=1695993753; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=0ZUDwhKFgzYJK+uqdrp6neE6oFhWPa0GOt7/vDTdL78=; b=IgUgmutD8wpoG8GXpnA/H5NdsRE6U55qX51hvyXA4etMihkah4bO0JYbdTBMv6bidK FYwhqOjln9pIdkpN+BvWSRDy9uI3WJIeSEPVazZEYw09bpX45IdnmlZysXtIu5PHB9XB Qcj84PCpCFCNziGsIA/GYs/+SceNhwMkrrIi8IOsrjaHReNDcM6g2ne6vP+/mK+x33Fc CEckUYny0ctuvsTZjjHMGd16BPNYj+UxgY+1jGrKOQa0qdsJjbD8/+Df6fViJjNeF5lp RDl345t/Ow7fQwsqWkJ+5Nf0OHaZqNiqlZlS+31EiZz6FlgcYdN2fH8vZ2ym4BMc/LC/ iHJg== X-Gm-Message-State: AOJu0Yw70hEzm+/rOVQIyylX/pXfb3F9l4tTzax79qjhFMVLaFnDKAbk nX2uhRyaHn2HV5GtsMHoV6U= X-Google-Smtp-Source: AGHT+IGTSiv/ZZ0yqP+wWcZRZGNZtO1GyUWY9pK3ZM8jgaxkudPiXhrvhn4rqw6ngc3zzjbKLv3kbQ== X-Received: by 2002:a17:906:bca:b0:9a1:fab3:ee43 with SMTP id y10-20020a1709060bca00b009a1fab3ee43mr8248478ejg.0.1695388953261; Fri, 22 Sep 2023 06:22:33 -0700 (PDT) Received: from smtpclient.apple (2001-1c04-3c07-1a00-30bb-297a-b1cc-8861.cable.dynamic.v6.ziggo.nl. [2001:1c04:3c07:1a00:30bb:297a:b1cc:8861]) by smtp.gmail.com with ESMTPSA id ce21-20020a170906b25500b009ad850d4760sm2685212ejb.219.2023.09.22.06.22.32 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 22 Sep 2023 06:22:32 -0700 (PDT) From: Frits Hoogland Message-Id: <76AE4C54-47E6-45F1-AC7B-129C8CAA88E0@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_62C6F2F5-02CF-4593-8DA1-69C9AF4ED025" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\)) Subject: Re: Dirty reads on index scan, Date: Fri, 22 Sep 2023 15:22:22 +0200 In-Reply-To: Cc: Laurenz Albe , pgsql-performance@postgresql.org To: Koen De Groote References: X-Mailer: Apple Mail (2.3731.700.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_62C6F2F5-02CF-4593-8DA1-69C9AF4ED025 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 The actual thing that might be good to see is the query plan (explain). It is commonly regarded an issue to select =E2=80=98*=E2=80=99, in many = cases only a subset of the rows are needed, but I don=E2=80=99t know = your exact case. If a limited number of columns are actually needed from the table, it = might help to create an index which has got all the columns in the = index, either directly for the index, or included with the index. This is called a covering index, and could prevent the need to read the = actual table, which is visible by the row source 'index only scan=E2=80=99= . But that potential can only be assessed by looking at the explain = output. A covering index needs the visibility map to be recent for the blocks, = otherwise a table visit must be done to get the latest tuple state. This = can be done by vacuuming. When your query is as efficient as it can be, there are two things left. One is that blocks in the database buffer cache that are not frequently = accessed will age out in favour of blocks that are accessed more = recently.=20 On the operating system, the same mechanism takes place, postgres reads = data buffered, which means the operating system caches the IOs for the = database blocks too. This means that if you query data that is stored in blocks that are not = recently used, these will not be present in the database cache, and not = in the operating system cache, and thus require a physical IO from disk = to be obtained. If the amount of blocks relative to the caches is = modest, another execute of the same SQL can take advantage, and thus = result in much lower latency. You describe the query to be using a timestamp. If the timestamp moves = forward in time, and the amount of data is equal over time, then the = latency for the two scenario=E2=80=99s should remain stable.=20 If the amount of data increases over time, and thus more blocks are = needed to be read because more rows are stored that needs scanning to = get a result, then the latency will increase. Frits Hoogland > On 22 Sep 2023, at 10:35, Koen De Groote wrote: >=20 > Alright. >=20 > So, if I want to speed up the query, apart from trying to vacuum it = beforehand, I suspect I've hit the limit of what this query can do? >=20 > Because, the table is just going to keep growing. And it's a usually a = query that runs one time per day, so it's a cold run each time. >=20 > Is this just going to get slower and slower and there's nothing that = can be done about it? >=20 > Regards, > Koen De Groote >=20 >=20 >=20 > On Thu, Sep 21, 2023 at 9:30=E2=80=AFPM Laurenz Albe = > wrote: >> 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 =3D true and = hasbeenverified =3D true and insert_timestamp <=3D '2023-09-01 = 00:00:00.000' limit 1000; >> >=20 >> > The date is an example, it is the format that is used in the query. >> >=20 >> > The table has 81M rows. Is 50GB in size. And the index is 34MB >> >=20 >> > The index is as follows: >> > btree (insert_timestamp DESC) WHERE hasbeenchecked =3D true AND = hasbeenverified =3D true >> >=20 >> > I'm seeing a slow query first, then a fast one, and if I move the = date, a slow query again. >> >=20 >> > What I'm seeing is: >> > Attempt 1: >> > Hit: 5171(40MB) >> > Read: 16571(130MB) >> > Dirtied: 3940(31MB) >> >=20 >> > Attempt 2: >> > Hit: 21745 (170MB) >> > Read: Nothing >> > Dirtied: Nothing. >> >=20 >> > 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. >>=20 >> That's normal behavior: after the first execution, the data are = cached, so the query >> becomes much faster. >>=20 >> 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. >>=20 >> Yours, >> Laurenz Albe --Apple-Mail=_62C6F2F5-02CF-4593-8DA1-69C9AF4ED025 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 The actual = thing that might be good to see is the query plan (explain).
It is = commonly regarded an issue to select =E2=80=98*=E2=80=99, in many cases = only a subset of the rows are needed, but I don=E2=80=99t know your = exact case.
If a limited number of columns are actually needed = from the table, it might help to create an index which has got all the = columns in the index, either directly for the index, or included with = the index.
This is called a covering index, and could prevent = the need to read the actual table, which is visible by the row source = 'index only scan=E2=80=99.
But that potential can only be = assessed by looking at the explain output.

A = covering index needs the visibility map to be recent for the blocks, = otherwise a table visit must be done to get the latest tuple state. This = can be done by vacuuming.

When your query is as = efficient as it can be, there are two things left.
One is that = blocks in the database buffer cache that are not frequently accessed = will age out in favour of blocks that are accessed more = recently. 
On the operating system, the same mechanism = takes place, postgres reads data buffered, which means the operating = system caches the IOs for the database blocks = too.

This means that if you query data that is = stored in blocks that are not recently used, these will not be present = in the database cache, and not in the operating system cache, and thus = require a physical IO from disk to be obtained. If the amount of blocks = relative to the caches is modest, another execute of the same SQL can = take advantage, and thus result in much lower = latency.

You describe the query to be using a = timestamp. If the timestamp moves forward in time, and the amount of = data is equal over time, then the latency for the two scenario=E2=80=99s = should remain stable. 
If the amount of data increases = over time, and thus more blocks are needed to be read because more rows = are stored that needs scanning to get a result, then the latency will = increase.

Frits = Hoogland




On 22 Sep 2023, at 10:35, Koen = De Groote <kdg.dev@gmail.com> wrote:

Alright.

So, if I want to = speed up the query, apart from trying to vacuum it beforehand, I suspect = I've hit the limit of what this query can = do?

Because, the table is just going to keep = growing. And it's a usually a query that runs one time per day, so it's = a cold run each time.

Is this just going to get = slower and slower and there's nothing that can be done about = it?

Regards,
Koen De = Groote



On Thu, Sep = 21, 2023 at 9:30=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> = wrote:
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 =3D true and = hasbeenverified =3D true and insert_timestamp <=3D '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 =3D true = AND hasbeenverified =3D 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

= --Apple-Mail=_62C6F2F5-02CF-4593-8DA1-69C9AF4ED025--