On Tue, 20 May 2025, 22:14 Peter Geoghegan, wrote:
>
> On Mon, May 12, 2025 at 8:58 AM Peter Geoghegan wrote:
> > I wonder if we can fix this problem by getting rid of the old support
> > routine #5, "options". It currently doesn't do anything, and I always
> > thought it was strange that it was
On Mon, May 12, 2025 at 8:58 AM Peter Geoghegan wrote:
> I wonder if we can fix this problem by getting rid of the old support
> routine #5, "options". It currently doesn't do anything, and I always
> thought it was strange that it was added "for uniformity" with other
> index AMs.
Attached patch
On Sun, May 11, 2025 at 11:09 PM Tomas Vondra wrote:
> I where "revert" is master with the removal patch. Sorry about the
> confusion, I guess I was distracted and did some mistake.
>
> So, this seems to be in line with the hypothesis ...
That makes way more sense.
I wonder if we can fix this pr
On 5/11/25 18:07, Peter Geoghegan wrote:
> On Sat, May 10, 2025 at 10:59 AM Tomas Vondra wrote:
>> But doesn't it also highlight how fragile this memory allocation is? The
>> skip scan patch didn't do anything wrong - it just added a couple
>> fields, using a little bit more memory. I think we
On Sat, May 10, 2025 at 10:59 AM Tomas Vondra wrote:
> But doesn't it also highlight how fragile this memory allocation is? The
> skip scan patch didn't do anything wrong - it just added a couple
> fields, using a little bit more memory. I think we understand allocating
> more memory may need more
On 5/10/25 13:14, Matthias van de Meent wrote:
> ...
>
> I've attached a patch that makes IndexAmRoutine a static const*,
> removing it from rd_indexcxt, and returning some of the index ctx
> memory usage to normal:
>
> count (patch 1) | total_bytes | combined_size
> -+--
On Sat, 10 May 2025 at 00:54, Tomas Vondra wrote:
>
> On 5/9/25 23:30, Matthias van de Meent wrote:
> > ...
> >> The difference shown by your flame graph is absolutely enormous --
> >> that's *very* surprising to me. btbeginscan and btrescan go from being
> >> microscopic to being very prominent.
On 5/9/25 23:30, Matthias van de Meent wrote:
> ...
>> The difference shown by your flame graph is absolutely enormous --
>> that's *very* surprising to me. btbeginscan and btrescan go from being
>> microscopic to being very prominent. But skip scan simply didn't touch
>> either function, at all, d
On Fri, 9 May 2025 at 20:38, Peter Geoghegan wrote:
>
> On Fri, May 9, 2025 at 2:04 PM Tomas Vondra wrote:
> > Yes, I'm sure it's doing index only scan
>
> Looks that way, from the pair of flame graphs you sent. Thanks for that.
>
> > did you update "bid" or did
> > you leave it as generated by "
On Fri, May 9, 2025 at 12:29 PM Tomas Vondra wrote:
> Tried, doesn't seem to affect the results at all.
OK, then. I don't think that we're going to figure it out this side of
pgConf.dev. I'm already well behind on talk preparation.
--
Peter Geoghegan
On Fri, May 9, 2025 at 2:04 PM Tomas Vondra wrote:
> Yes, I'm sure it's doing index only scan
Looks that way, from the pair of flame graphs you sent. Thanks for that.
> did you update "bid" or did
> you leave it as generated by "pgbench -i"?.
I didn't bother with updating, or running VACUUM FUL
On Fri, May 9, 2025 at 8:58 AM Tomas Vondra wrote:
> select count(*) from pgbench_accounts where bid = 0
What kind of plan are you getting? Are you sure it's index-only scans?
With 100 partitions, I get a parallel sequential scan when I run
EXPLAIN ANALYZE with this query from psql -- though o
On Fri, May 9, 2025 at 1:19 PM Tomas Vondra wrote:
> Not sure I understand. Why would it need to scan 85 index pages? There's
> only 100 matching tuples total, spread over the 100 partitions. We'll
> need to scan maybe 1 page per partition.
I was unclear. The thing about 85 leaf pages only applie
On 5/9/25 18:36, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 12:28 PM Tomas Vondra wrote:
>> Not sure if it matters, but this uses index-only scans, and the pages
>> are all-visible, so maybe it's not much more expensive.
>
> You're still going to have to scan 85 full index pages on your
> pg
On Fri, May 9, 2025 at 12:28 PM Tomas Vondra wrote:
> Not sure if it matters, but this uses index-only scans, and the pages
> are all-visible, so maybe it's not much more expensive.
You're still going to have to scan 85 full index pages on your
pgbench_accounts.bid index -- that's pretty expensiv
On 5/9/25 18:09, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 11:55 AM Peter Geoghegan wrote:
>> Note that "sizeof(FmgrInfo)" is 48 bytes. Prior to skip scan,
>> RelationData.rd_supportinfo would have required 48*5=240 bytes. After
>> skip scan, it would have required 48*6=288 bytes. Maybe 256
On 5/9/25 17:55, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 10:57 AM Tomas Vondra wrote:
>> I see the regression even with variants that actually match some rows.
>> For example if I do this:
>
>> so that the query matches 100 rows, I get the same behavior.
>
> That's really weird, since th
On Fri, May 9, 2025 at 11:55 AM Peter Geoghegan wrote:
> Note that "sizeof(FmgrInfo)" is 48 bytes. Prior to skip scan,
> RelationData.rd_supportinfo would have required 48*5=240 bytes. After
> skip scan, it would have required 48*6=288 bytes. Maybe 256 bytes is
> some kind of critical threshold, s
On Fri, May 9, 2025 at 10:57 AM Tomas Vondra wrote:
> I see the regression even with variants that actually match some rows.
> For example if I do this:
> so that the query matches 100 rows, I get the same behavior.
That's really weird, since the index scans will no longer be cheap.
And yet what
On 5/9/25 16:22, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:59 AM Peter Geoghegan wrote:
>> I don't actually think that this kind of scan would have been affected
>> by those known regressions -- since they don't use array keys. But it
>> is definitely true that the queries that you're look
On 5/9/25 16:17, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 8:58 AM Tomas Vondra wrote:
>> I'm also not sure about the root cause, but while investigating it one
>> of the experiments I tried was tweaking the glibc malloc by setting
>>
>> export MALLOC_TOP_PAD_=$((64*1024*1024))
>>
>>
On Fri, May 9, 2025 at 9:59 AM Peter Geoghegan wrote:
> I don't actually think that this kind of scan would have been affected
> by those known regressions -- since they don't use array keys. But it
> is definitely true that the queries that you're looking at very much
> rely on the optimization f
On Fri, May 9, 2025 at 8:58 AM Tomas Vondra wrote:
> I'm also not sure about the root cause, but while investigating it one
> of the experiments I tried was tweaking the glibc malloc by setting
>
> export MALLOC_TOP_PAD_=$((64*1024*1024))
>
> which keeps a 64MB "buffer" in glibc, to reduce the
On 5/9/25 15:59, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:42 AM Peter Geoghegan wrote:
>> I'm rather puzzled as to why this happens, then. I expect that nbtree
>> preprocessing will be able to use its usual single index column/index
>> key fast path here -- the "We can short-circuit most
On Fri, May 9, 2025 at 9:33 AM Peter Geoghegan wrote:
> Can you try it again, with prepared statements? Alternatively, you
> could selectively revert the changes that commit 92fe23d93aa made to
> utils/adt/selfuncs.c, and then retest.
Oh, wait, you already did try it with prepared statements.
I'
On Fri, May 9, 2025 at 9:42 AM Peter Geoghegan wrote:
> I'm rather puzzled as to why this happens, then. I expect that nbtree
> preprocessing will be able to use its usual single index column/index
> key fast path here -- the "We can short-circuit most of the work if
> there's just one key" path i
On Fri, May 9, 2025 at 8:58 AM Tomas Vondra wrote:
> My conclusion from this is that 92fe23d93aa ends up doing a lot of
> malloc calls, and this is what makes causes the regression. Otherwise
> setting the MALLOC_TOP_PAD_ would not help like this. But I haven't
> looked at the code, and I wouldn't
Hi,
While doing some benchmarks to compare 17 vs. 18, I ran into a
regression that I ultimately tracked to commit 92fe23d93aa.
commit 92fe23d93aa3bbbc40fca669cabc4a4d7975e327
Author: Peter Geoghegan
Date: Fri Apr 4 12:27:04 2025 -0400
Add nbtree skip scan optimization.
The wo
On Tue, May 6, 2025 at 8:11 PM Mark Dilger wrote:
> I have been using your two patches, one committed by you and the other
> committed locally to my working directory, since you posted them a few days
> ago. I have not encountered any of the problems that I had been encountering
> previously.
On Tue, May 6, 2025 at 3:01 PM Peter Geoghegan wrote:
>
> I plan to commit everything in the next couple of days, barring any
> objections.
>
I have been using your two patches, one committed by you and the other
committed locally to my working directory, since you posted them a few days
ago. I
On Fri, May 2, 2025 at 3:04 PM Peter Geoghegan wrote:
> I would like to commit the first patch later today, ahead of shipping
> beta1. But the second patch won't make it into beta1.
Committed the first patch last Friday.
Attached is v2, whose 0002- bugfix patch is essentially unchanged
compared
On Fri, May 2, 2025 at 2:22 PM Peter Geoghegan wrote:
> A slight variant of my fuzzing Python script did in fact go on to
> detect a couple of bugs.
>
> I'm attaching a compressed SQL file with repros for 2 different bugs.
> The first bug was independently detected by some kind of fuzzing
> perfor
On Wed, Apr 30, 2025 at 9:12 PM Mark Dilger
wrote:
> TRAP: failed Assert("numSkipArrayKeys == 0"), File: "nbtpreprocesskeys.c",
> Line: 1859, PID: 7210
> 0 postgres0x0001032f30e0
> ExceptionalCondition + 108
> 1 postgres0x000102
Peter,
moving the conversation here from "pgsql: Improve nbtree skip scan
primitive scan scheduling" thread on -committers. Attached is another
regression test for your consideration, which gives rise to the following
assertion:
TRAP: failed Assert("numSkipArrayKeys == 0"), File: "nbtpreprocessk
On Tue, Apr 1, 2025 at 7:02 PM Peter Geoghegan wrote:
> Though I think it should be "" safe even when "key->sk_attno >
> firstchangingattnum" "", to highlight that the rule here is
> significantly more permissive than even the nearby range skip array
> case, which is still safe when (key->sk_attno
On Tue, Apr 1, 2025 at 3:08 PM Alena Rybakina wrote:
> I think it would be useful to show information that we used an index scan but
> at the same time we skipped the "region" column and I assume we should output
> how many distinct values the "region" column had.
>
> For example it will look li
On Tue, Apr 1, 2025 at 4:16 PM Matthias van de Meent
wrote:
> While I agree that there is such a cost, I don't think that this is
> too far fetched. They are not just added when we have SAOP scankeys,
> and I think the non-SAOP case is one of the most important areas where
> this patch improves pe
On 12.03.2025 23:50, Peter Geoghegan wrote:
On Wed, Mar 12, 2025 at 4:28 PM Alena Rybakina
wrote:
Thank you for the explanation!
Now I see why these changes were made.
After your additional explanations, everything really became clear and I
fully agree with the current code regarding this p
On 03.04.2025 02:32, Peter Geoghegan wrote:
On Tue, Apr 1, 2025 at 3:08 PM Alena Rybakina wrote:
I think it would be useful to show information that we used an index scan but at the same time we
skipped the "region" column and I assume we should output how many distinct values the
"region" co
On Tue, Apr 1, 2025 at 5:56 PM Matthias van de Meent
wrote:
> 0002:
>
> // nbtutils.c
>
> > + * (safe even when "key->sk_attno <= firstchangingattnum")
>
> Typo: should be "key->sk_attno >= firstchangingattnum".
Good catch!
Though I think it should be "" safe even when "key->sk_attno
On Fri, 28 Mar 2025 at 22:59, Peter Geoghegan wrote:
>
> On Tue, Mar 25, 2025 at 7:45 PM Peter Geoghegan wrote:
> > Attached is v31, which has a much-improved _bt_skip_ikeyprefix (which
> > I've once again renamed, this time to _bt_set_startikey).
>
> Attached is v32
Thanks!
The review below wa
On Tue, 1 Apr 2025 at 04:02, Peter Geoghegan wrote:
>
> On Fri, Mar 28, 2025 at 5:59 PM Peter Geoghegan wrote:
> > Attached is v32, which has very few changes, but does add a new patch:
> > a patch that adds skip-array-specific primitive index scan heuristics
> > to _bt_advance_array_keys (this i
On Tue, 1 Apr 2025 at 23:56, Matthias van de Meent
wrote:
>
> On Tue, 1 Apr 2025 at 04:02, Peter Geoghegan wrote:
> >
> > On Fri, Mar 28, 2025 at 5:59 PM Peter Geoghegan wrote:
> > > Attached is v32, which has very few changes, but does add a new patch:
> > > a patch that adds skip-array-specifi
On Tue, Apr 1, 2025 at 10:40 AM Matthias van de Meent
wrote:
> The review below was started for v31, then adapted to v32 when that
> arrived. I haven't cross-checked this with v33.
There's been hardly any changes to 0001- in quite a while, so that's fine.
> > Teach nbtree composite index scans t
On Tue, 1 Apr 2025 at 21:02, Peter Geoghegan wrote:
>
> On Tue, Apr 1, 2025 at 10:40 AM Matthias van de Meent
> wrote:
> > > When nbtree is passed input scan keys derived from a
> > > query predicate "WHERE b = 5", new nbtree preprocessing steps now output
> > > "WHERE a = ANY() AND b = 5" scan k
Hi! Sorry for my later feedback, I didn't have enough time because of my
work and the conference that was held during these two days.
On 28.03.2025 23:15, Peter Geoghegan wrote:
On Thu, Mar 27, 2025 at 6:03 PM Alena Rybakina
wrote:
I replied an example like this:
This example shows costs th
On 01.04.2025 17:39, Matthias van de Meent wrote:
On Fri, 28 Mar 2025 at 22:59, Peter Geoghegan wrote:
On Tue, Mar 25, 2025 at 7:45 PM Peter Geoghegan wrote:
Attached is v31, which has a much-improved _bt_skip_ikeyprefix (which
I've once again renamed, this time to _bt_set_startikey).
Attach
On Tue, Apr 1, 2025 at 9:26 AM Aleksander Alekseev
wrote:
> Can you think of any tests specifically for 0003, or relying on the
> added Asserts() is best we can do? Same question for 0002.
There are many more tests than those that are included in the patch. I
wrote and debugged all patches using
Hi Peter,
> I'm now very close to committing everything. Though I do still want
> another pair of eyes on the newer
> 0003-Improve-skip-scan-primitive-scan-scheduling.patch stuff before
> commiting (since I still intend to commit all the remaining patches
> together).
Can you think of any tests s
On Thu, Mar 27, 2025 at 6:03 PM Alena Rybakina
wrote:
> I replied an example like this:
This example shows costs that are dominated by heap access costs. Both
the sequential scan and the bitmap heap scan must access 637 heap
blocks. So I don't think that this is such a great example -- the heap
a
Hi!
On 26.03.2025 02:45, Peter Geoghegan wrote:
On Sat, Mar 22, 2025 at 1:47 PM Peter Geoghegan wrote:
Attached is v30, which fully replaces the pstate.prechecked
optimization with the new _bt_skip_ikeyprefix optimization (which now
appears in v30-0002-Lower-nbtree-skip-array-maintenance-overh
On Mon, Mar 17, 2025 at 6:51 PM Matthias van de Meent
wrote:
> This hasn't changed meaningfully in this patch, but I noticed that
> pstate.finaltup is never set for the final page of the scan direction
> (i.e. P_RIGHTMOST or P_LEFTMOST for forward or backward,
> respectively). If it isn't used mor
On Tue, Mar 18, 2025 at 1:01 PM Matthias van de Meent
wrote:
> My comments on 0004:
>
> > _bt_skiparray_strat_decrement
> > _bt_skiparray_strat_increment
>
> In both functions the generated value isn't used when the in/decrement
> overflows (and thus invalidates the qual), or when the opclass some
On Wed, Mar 19, 2025 at 5:08 PM Peter Geoghegan wrote:
> The tricky logic added by 0003-* is my single biggest outstanding
> concern about the patch series. Particularly its potential to confuse
> the existing invariants for required arrays. And particularly in light
> of the changes that I made t
On Mon, 17 Mar 2025 at 23:51, Matthias van de Meent
wrote:
>
> On Tue, 11 Mar 2025 at 16:53, Peter Geoghegan wrote:
> >
> > On Sat, Mar 8, 2025 at 11:43 AM Peter Geoghegan wrote:
> > > I plan on committing this one soon. It's obviously pretty pointless to
> > > make the BTMaxItemSize operate off
On Tue, Mar 18, 2025 at 9:37 AM Alena Rybakina
wrote:
> Sorry, I figured it out. The Assert was added to avoid misuse of the function
> to reinitialize memory and to ensure that it happens when parallel_aware is
> positive.
Yeah. The assertion is supposed to suggest "don't worry, I didn't
forge
On 18.03.2025 13:54, Alena Rybakina wrote:
On 12.03.2025 23:50, Peter Geoghegan wrote:
On Wed, Mar 12, 2025 at 4:28 PM Alena Rybakina
wrote:
Thank you for the explanation!
Now I see why these changes were made.
After your additional explanations, everything really became clear and I
full
On Tue, 11 Mar 2025 at 16:53, Peter Geoghegan wrote:
>
> On Sat, Mar 8, 2025 at 11:43 AM Peter Geoghegan wrote:
> > I plan on committing this one soon. It's obviously pretty pointless to
> > make the BTMaxItemSize operate off of a page header, and not requiring
> > it is more flexible.
>
> Commit
On Wed, Mar 12, 2025 at 4:28 PM Alena Rybakina
wrote:
> Thank you for the explanation!
>
> Now I see why these changes were made.
>
> After your additional explanations, everything really became clear and I
> fully agree with the current code regarding this part.
Cool.
> However I did not see an
On 12.03.2025 01:59, Peter Geoghegan wrote:
On Tue, Mar 11, 2025 at 6:24 PM Alena Rybakina
wrote:
Hi, reviewing the code I noticed that you removed the
parallel_aware check for DSM initialization for BitmapIndexScan,
IndexScan, IndexOnlyScan,
but you didn't do the same in the ExecParallelReInit
On Tue, Mar 11, 2025 at 6:24 PM Alena Rybakina
wrote:
> Hi, reviewing the code I noticed that you removed the
> parallel_aware check for DSM initialization for BitmapIndexScan,
> IndexScan, IndexOnlyScan,
> but you didn't do the same in the ExecParallelReInitializeDSM function
> and I can't figure
On 11.03.2025 18:52, Peter Geoghegan wrote:
On Sat, Mar 8, 2025 at 11:43 AM Peter Geoghegan wrote:
I plan on committing this one soon. It's obviously pretty pointless to
make the BTMaxItemSize operate off of a page header, and not requiring
it is more flexible.
Committed. And committed a revis
On Fri, Jan 24, 2025 at 10:38 PM Peter Geoghegan wrote:
> I can reproduce this. However, it should be noted that the regression
> completely goes away if I make one small change to your test case: all
> I need to do is make sure that the CREATE INDEX happens *before*
> inserting any rows into the
On Fri, Jan 24, 2025 at 10:07 PM Heikki Linnakangas wrote:
> On my laptop, this is the worst case I could come up with:
>
> create table skiptest as select g / 10 as a, g%10 as b from
> generate_series(1, 1000) g;
> vacuum freeze skiptest;
> create index on skiptest (a, b);
>
> set enable_seqs
On 03/01/2025 21:43, Peter Geoghegan wrote:
The newly revised "skipskip" optimization seems to get the regressions
down to only a 5% - 10% increase in runtime across a wide variety of
unsympathetic cases -- I'm now validating performance against a test
suite based on the adversarial cases present
On 2024-11-26 07:32, Peter Geoghegan wrote:
On Mon, Nov 25, 2024 at 4:07 AM Masahiro Ikeda
wrote:
One thing I am concerned about is that it reduces the cases where the
optimization of _bt_checkkeys_look_ahead() works effectively, as the
approach
skips the skip immediately on the first occurrenc
On 2024-11-23 07:34, Peter Geoghegan wrote:
On Fri, Nov 22, 2024 at 4:17 AM Masahiro Ikeda
wrote:
Though the change fixes the assertion error in 'make check', there are
still
cases where the number of return values is incorrect. I would also
like
to
continue investigating.
Thanks for taking
On 2024-11-21 17:47, Masahiro Ikeda wrote:
On 2024-11-21 04:40, Peter Geoghegan wrote:
diff --git a/src/backend/access/nbtree/nbtutils.c
b/src/backend/access/nbtree/nbtutils.c
index b70b58e0c..ddae5f2a1 100644
--- a/src/backend/access/nbtree/nbtutils.c
+++ b/src/backend/access/nbtree/nbtutils.c
@
On 2024-11-21 04:40, Peter Geoghegan wrote:
On Wed, Nov 20, 2024 at 4:04 AM Masahiro Ikeda
wrote:
Thanks for your quick response!
Attached is v16. This is similar to v15, but the new
v16-0003-Fix-regressions* patch to fix the regressions is much less
buggy, and easier to understand.
Unlike v
Hi Peter,
On 2024-11-20 04:06, Peter Geoghegan wrote:
Hi Masahiro,
On Tue, Nov 19, 2024 at 3:30 AM Masahiro Ikeda
wrote:
Apologies for the delayed response. I've confirmed that the costing is
significantly
improved for multicolumn indexes in the case I provided. Thanks!
https://www.postgresq
Hi,
Apologies for the delayed response. I've confirmed that the costing is
significantly
improved for multicolumn indexes in the case I provided. Thanks!
https://www.postgresql.org/message-id/TYWPR01MB10982A413E0EC4088E78C0E11B1A62%40TYWPR01MB10982.jpnprd01.prod.outlook.com
I have some comment
On Mon, Nov 4, 2024 at 4:58 PM Matthias van de Meent
wrote:
> This is a review on v11, not the latest v13. I suspect most comments
> still apply, but I haven't verified this.
v11 is indeed quite similar to v13, so this shouldn't really matter.
> I'm a bit concerned about the additional operation
On Fri, 18 Oct 2024 at 18:17, Peter Geoghegan wrote:
>
> On Wed, Oct 16, 2024 at 1:14 PM Peter Geoghegan wrote:
> > Attached is v10, which is another revision that's intended only to fix
> > bit rot against the master branch. There are no notable changes to
> > report.
>
> Attached is v11, which
On Fri, Sep 20, 2024 at 10:59 AM Peter Geoghegan wrote:
> On Fri, Sep 20, 2024 at 10:07 AM Tomas Vondra wrote:
> > Yes, I think backpatching to 17 would be fine. I'd be worried about
> > maybe disrupting some monitoring in production systems, but for 17 that
> > shouldn't be a problem yet. So fin
On Fri, Sep 20, 2024 at 10:07 AM Tomas Vondra wrote:
> Yes, I think backpatching to 17 would be fine. I'd be worried about
> maybe disrupting some monitoring in production systems, but for 17 that
> shouldn't be a problem yet. So fine with me.
I'll commit minimal changes to _bt_first that at leas
On 9/20/24 16:21, Peter Geoghegan wrote:
> On Fri, Sep 20, 2024 at 9:45 AM Tomas Vondra wrote:
>> 3) restart cluster, drop caches
>>
>> 4) run the query from the SQL script
>>
>> I suspect you don't do (3). I didn't mention this explicitly, my message
>> only said "with uncached data", so maybe th
On Fri, Sep 20, 2024 at 9:45 AM Tomas Vondra wrote:
> 3) restart cluster, drop caches
>
> 4) run the query from the SQL script
>
> I suspect you don't do (3). I didn't mention this explicitly, my message
> only said "with uncached data", so maybe that's the problem?
You're right that I didn't do
On 9/18/24 20:52, Peter Geoghegan wrote:
> On Wed, Sep 18, 2024 at 7:36 AM Tomas Vondra wrote:
>> Makes sense. I started with the testing before before even looking at
>> the code, so it's mostly a "black box" approach. I did read the 1995
>> paper before that, and the script generates queries wit
On 9/19/24 21:22, Peter Geoghegan wrote:
> On Mon, Sep 16, 2024 at 6:05 PM Tomas Vondra wrote:
>> For example, one of the slowed down queries is query 702 (top of page 8
>> in the PDF). The query is pretty simple:
>>
>> explain (analyze, timing off, buffers off)
>> select id1,id2 from t_10
On Mon, Sep 16, 2024 at 6:05 PM Tomas Vondra wrote:
> For example, one of the slowed down queries is query 702 (top of page 8
> in the PDF). The query is pretty simple:
>
> explain (analyze, timing off, buffers off)
> select id1,id2 from t_100_1000_1_2
>where NOT (id1 in (:list)) AND (
On Wed, Sep 18, 2024 at 7:36 AM Tomas Vondra wrote:
> Makes sense. I started with the testing before before even looking at
> the code, so it's mostly a "black box" approach. I did read the 1995
> paper before that, and the script generates queries with clauses
> inspired by that paper, in particu
On 9/18/24 00:14, Peter Geoghegan wrote:
> On Mon, Sep 16, 2024 at 6:05 PM Tomas Vondra wrote:
>> I've been looking at this patch over the couple last days, mostly doing
>> some stress testing / benchmarking (hence the earlier report) and basic
>> review.
>
> Thanks for taking a look! Very helpfu
On Mon, Sep 16, 2024 at 6:05 PM Tomas Vondra wrote:
> I've been looking at this patch over the couple last days, mostly doing
> some stress testing / benchmarking (hence the earlier report) and basic
> review.
Thanks for taking a look! Very helpful.
> I do have some initial review comments, and
On Mon, Sep 16, 2024 at 3:13 PM Peter Geoghegan wrote:
> I agree with your approach, but I'm concerned about it causing
> confusion inside _bt_parallel_done. And so I attach a v2 revision of
> your bug fix. v2 adds a check that nails that down, too.
Pushed this just now.
Thanks
--
Peter Geogheg
On Thu, Sep 12, 2024 at 10:49 AM Matthias van de Meent
wrote:
> Thanks to Peter for the description, that helped me debug the issue. I
> think I found a fix for the issue: regression tests for 811af978
> consistently got stuck on my macbook before the attached patch 0001,
> after applying that thi
On 9/12/24 16:49, Matthias van de Meent wrote:
> On Mon, 9 Sept 2024 at 21:55, Peter Geoghegan wrote:
>>
> ...
>
> The fix in 0001 is relatively simple: we stop backends from waiting
> for a concurrent backend to resolve the NEED_PRIMSCAN condition, and
> instead move our local state machine so t
On Mon, 9 Sept 2024 at 21:55, Peter Geoghegan wrote:
>
> On Sat, Sep 7, 2024 at 11:27 AM Tomas Vondra wrote:
> > I started looking at this patch today.
>
> Thanks for taking a look!
>
> > The first thing I usually do for
> > new patches is a stress test, so I did a simple script that generates
>
On Sat, Sep 7, 2024 at 11:27 AM Tomas Vondra wrote:
> I started looking at this patch today.
Thanks for taking a look!
> The first thing I usually do for
> new patches is a stress test, so I did a simple script that generates
> random table and runs a random query with IN() clause with various
>
Hi,
I started looking at this patch today. The first thing I usually do for
new patches is a stress test, so I did a simple script that generates
random table and runs a random query with IN() clause with various
configs (parallel query, index-only scans, ...). And it got stuck on a
parallel query
On Sat, Aug 3, 2024 at 6:14 PM Peter Geoghegan wrote:
> Displaying the number of primitive scans would already be useful for
> index scans with SAOPs, even without this patch. The same general
> concepts (estimated vs. actual primitive index scans) already exist,
> as of Postgres 17. That's really
On Sat, Aug 3, 2024 at 3:34 PM Dmitry Dolgov <9erthali...@gmail.com> wrote:
> I see that the patch is not supposed to deal with aggregates in any special
> way.
Right.
> But from what I understand after a quick review, skip scan is not getting
> applied to them if there are no quals in the query
> On Wed, Jun 26, 2024 at 03:16:07PM GMT, Peter Geoghegan wrote:
>
> Loose index scan is a far more specialized technique than skip scan.
> It only applies within special scans that feed into a DISTINCT group
> aggregate. Whereas my skip scan patch isn't like that at all -- it's
> much more general
Hi,
Since I'd like to understand the skip scan to improve the EXPLAIN output
for multicolumn B-Tree Index[1], I began to try the skip scan with some
queries and look into the source code.
I have some feedback and comments.
(1)
At first, I was surprised to look at your benchmark result because t
On Fri, Jul 5, 2024 at 8:44 PM Peter Geoghegan wrote:
> CREATE INDEX test4_idx ON test4 USING btree(((extract(year from d))::int4),n);
>
> This performs much better. Now I see "DEBUG: skipping 1 index
> attributes" when I run the query "EXPLAIN (ANALYZE, BUFFERS) SELECT
> COUNT(*) FROM test4 WHER
On Fri, Jul 5, 2024 at 7:04 AM Aleksander Alekseev
wrote:
> Test2 with "char" doesn't seem to benefit from the patch anymore
> (pretty sure it did in v1). It always chooses Parallel Seq Scans even
> if I change the condition to `WHERE n > 999_995_000` or `WHERE n =
> 999_997_362`. Is it an expecte
Hi Peter,
> It looks like the queries you posted have a kind of adversarial
> quality to them, as if they were designed to confuse the
> implementation. Was it intentional?
To some extent. I merely wrote several queries that I would expect
should benefit from skip scans. Since I didn't look at th
On Tue, Jul 2, 2024 at 12:55 PM Peter Geoghegan wrote:
> Although v2 gives correct answers to the queries, the scan itself
> performs an excessive amount of leaf page accesses. In short, it
> behaves just like a full index scan would, even though we should
> expect it to skip over significant runs
On Tue, Jul 2, 2024 at 12:25 PM Peter Geoghegan wrote:
> Attached v2 fixes this bug. The problem was that the skip support
> function used by the "char" opclass assumed signed char comparisons,
> even though the authoritative B-Tree comparator (support function 1)
> uses signed comparisons (via ui
On Tue, Jul 2, 2024 at 9:30 AM Peter Geoghegan wrote:
> > EXPLAIN [ANALYZE] SELECT COUNT(*) FROM test1 WHERE n > 900_000_000;
>
> For example, this first test query goes from needing a full index scan
> that has 5056 buffer hits to a skip scan that requires only 12 buffer
> hits.
Actually, looks
On Tue, Jul 2, 2024 at 8:53 AM Aleksander Alekseev
wrote:
> CREATE TABLE test1(c char, n bigint);
> CREATE INDEX test1_idx ON test1 USING btree(c,n);
The type "char" (note the quotes) is different from char(1). It just
so happens that v1 has support for skipping attributes that use the
default op
1 - 100 of 101 matches
Mail list logo
