Thread

1. Dirty reads on index scan,

   Koen De Groote <kdg.dev@gmail.com> — 2023-09-21T15:05:06Z

   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
   

2. Re: Dirty reads on index scan,

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-21T19:30:37Z

   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
   
   
   
   

3. Re: Dirty reads on index scan,

   Koen De Groote <kdg.dev@gmail.com> — 2023-09-22T08:35:12Z

   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 PM 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 = 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
   >
   

4. Re: Dirty reads on index scan,

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-09-22T09:14:27Z

   On Fri, 2023-09-22 at 10:35 +0200, Koen De Groote wrote:
   > On Thu, Sep 21, 2023 at 9:30 PM 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 = 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.
   >
   > 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?
   
   Essentially yes.
   
   If the table does not have too many columns, or you can be more selective than "SELECT *",
   you could use an index-only scan with an index like
   
     CREATE INDEX ON my_table (insert_timestamp)
        INCLUDE (/* all the columns in the SELECT list */)
        WHERE hasbeenchecked AND hasbeenverified;
   
     VACUUM my_table;
   
   You need to configure autovacuum so that it vacuums the table often enough if you want
   an efficient index-only scan.
   
   If that is not feasible, you can gain speed by clustering the table.  For that, you need
   a different index:
   
     CREATE INDEX ckuster_idx ON my_table (hasbeenchecked, hasbeenverified, insert_timestamp);
   
     CLUSTER my_table USING cluster_idx;  -- attention: rewrites the table
   
   That should speed up the query considerably, because it will have to read way fewer pages
   from disk.  However, CLUSTER is not without problems.  Look at the documentation for the
   caveats.
   
   Yours,
   Laurenz Albe
   
   
   
   

5. Re: Dirty reads on index scan,

   Frits Hoogland <frits.hoogland@gmail.com> — 2023-09-22T13:22:22Z

   The actual thing that might be good to see is the query plan (explain).
   It is commonly regarded an issue to select ‘*’, in many cases only a subset of the rows are needed, but I don’t 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’.
   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’s 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 PM Laurenz Albe <laurenz.albe@cybertec.at <mailto: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 = 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
   
   

6. Re: Dirty reads on index scan,

   Koen De Groote <kdg.dev@gmail.com> — 2023-09-22T13:54:18Z

   The "select * " is a replacement for the actual fields, which are all
   queried. I simply want to avoid pasting the entire query. The names that
   are there, too, are edited.
   
   From what I'm reading, my best chance is to limit the amount of variables I
   need and change to index, plus tune for more frequent vacuuming of the
   table. I'll look into that.
   
   Thanks for the advice, both of you.
   
   Regards,
   Koen De Groote
   
   On Fri, Sep 22, 2023 at 3:22 PM Frits Hoogland <frits.hoogland@gmail.com>
   wrote:
   
   > The actual thing that might be good to see is the query plan (explain).
   > It is commonly regarded an issue to select ‘*’, in many cases only a
   > subset of the rows are needed, but I don’t 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’.
   > 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’s 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 PM 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 = 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
   >>
   >
   >
   

7. Re: Dirty reads on index scan,

   Jeff Janes <jeff.janes@gmail.com> — 2023-09-24T20:17:45Z

   On Fri, Sep 22, 2023 at 5:44 AM 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?
   >
   
   It is more a limit on the system as a whole, not just one query.  How is
   this table being inserted?  updated?  deleted? Is the physical row order
   correlated on the insert_timestamp column (look at pg_stats.correlation)?
   If not, why not? (Based on the name of the column, i would expect it to be
   highly correlated)
   
   Did you try the VACUUM and if so did it work?  Knowing that might help us
   figure out what else might work, even if you don't want to do the vacuum.
   But why not just do the vacuum?
   
   You should show us the actual plans, not just selected excerpts from it.
   There might be clues there that you haven't excerpted.  Turn on
   track_io_timing first if it is not on already.
   
   
   > 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.
   >
   
   Why do you care if a query run once per day takes 1 minute to run?
   
   
   > Is this just going to get slower and slower and there's nothing that can
   > be done about it?
   >
   
   It is probably not so much the size of the data (given that it is already
   far too large to stay in cache) as the number of dead tuples it had to wade
   through.  Having to read 16571 pages just to find 1000 tuples from a
   single-loop index scan suggests you have a lot of dead tuples.  Like, 16
   for every live tuple.  Why do you have so many, and why isn't index
   micro-vacuuming cleaning them up?  Do you have long-running transactions
   which are preventing clean up?  Are you running this on a standby?
   
   Cheers,
   
   Jeff