public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/1] Fix printing last progress report line in client programs. 8+ messages / 3 participants [nested] [flat]
* [PATCH 1/1] Fix printing last progress report line in client programs. @ 2020-08-12 20:27 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Heikki Linnakangas @ 2020-08-12 20:27 UTC (permalink / raw) A number of client programs have a "--progress" option that when printing to a TTY, updates the current line by printing a '\r' and overwriting it. After the last line, an extra '\n' needs to be printed to move the cursor to the next line. pg_basebackup and pgbench got this right, but pg_rewind and pg_checksums were slightly wrong. pg_rewind printed the newline to stdout instead of stderr, and pg_checksum printed the newline even when not printing to a TTY. Fix them. --- src/bin/pg_checksums/pg_checksums.c | 3 ++- src/bin/pg_rewind/pg_rewind.c | 3 ++- 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/src/bin/pg_checksums/pg_checksums.c b/src/bin/pg_checksums/pg_checksums.c index 1daa5aed0e0..9ae884897ef 100644 --- a/src/bin/pg_checksums/pg_checksums.c +++ b/src/bin/pg_checksums/pg_checksums.c @@ -626,7 +626,8 @@ main(int argc, char *argv[]) if (showprogress) { progress_report(true); - fprintf(stderr, "\n"); /* Need to move to next line */ + if (isatty(fileno(stderr))) + fprintf(stderr, "\n"); /* Need to move to next line */ } printf(_("Checksum operation completed\n")); diff --git a/src/bin/pg_rewind/pg_rewind.c b/src/bin/pg_rewind/pg_rewind.c index 0015d3b461a..c000c12fa58 100644 --- a/src/bin/pg_rewind/pg_rewind.c +++ b/src/bin/pg_rewind/pg_rewind.c @@ -422,7 +422,8 @@ main(int argc, char **argv) executeFileMap(); progress_report(true); - printf("\n"); + if (showprogress && isatty(fileno(stderr))) + fprintf(stderr, "\n"); /* Need to move to next line */ if (showprogress) pg_log_info("creating backup label and updating control file"); -- 2.20.1 --------------D7F229EC53544D857CE3A506-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-26 13:41 Peter Geoghegan <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Peter Geoghegan @ 2023-07-26 13:41 UTC (permalink / raw) To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Wed, Jul 26, 2023 at 5:29 AM Matthias van de Meent <[email protected]> wrote: > Considering that it caches/reuses the page across SAOP operations, can > (or does) this also improve performance for index scans on the outer > side of a join if the order of join columns matches the order of the > index? It doesn't really cache leaf pages at all. What it does is advance the array keys locally, while the original buffer lock is still held on that same page. > That is, I believe this caches (leaf) pages across scan keys, but can > (or does) it also reuse these already-cached leaf pages across > restarts of the index scan/across multiple index lookups in the same > plan node, so that retrieval of nearby index values does not need to > do an index traversal? I'm not sure what you mean. There is no reason why you need to do more than one single descent of an index to scan many leaf pages using many distinct sets of array keys. Obviously, this depends on being able to observe that we really don't need to redescend the index to advance the array keys, again and again. Note in particularly that this usually works across leaf pages. > I'm not sure I understand. MDAM seems to work on an index level to > return full ranges of values, while "skip scan" seems to try to allow > systems to signal to the index to skip to some other index condition > based on arbitrary cutoffs. This would usually be those of which the > information is not stored in the index, such as "SELECT user_id FROM > orders GROUP BY user_id HAVING COUNT(*) > 10", where the scan would go > though the user_id index and skip to the next user_id value when it > gets enough rows of a matching result (where "enough" is determined > above the index AM's plan node, or otherwise is impossible to > determine with only the scan key info in the index AM). I'm not sure > how this could work without specifically adding skip scan-related > index AM functionality, and I don't see how it fits in with this > MDAM/SAOP system. I think of that as being quite a different thing. Basically, the patch that added that feature had to revise the index AM API, in order to support a mode of operation where scans return groupings rather than tuples. Whereas this patch requires none of that. It makes affected index scans as similar as possible to conventional index scans. > > [...] > > > > Thoughts? > > MDAM seems to require exponential storage for "scan key operations" > for conditions on N columns (to be precise, the product of the number > of distinct conditions on each column); e.g. an index on mytable > (a,b,c,d,e,f,g,h) with conditions "a IN (1, 2) AND b IN (1, 2) AND ... > AND h IN (1, 2)" would require 2^8 entries. Note that I haven't actually changed anything about the way that the state machine generates new sets of single value predicates -- it's still just cycling through each distinct set of array keys in the patch. What you describe is a problem in theory, but I doubt that it's a problem in practice. You don't actually have to materialize the predicates up-front, or at all. Plus you can skip over them using the next index tuple. So skipping works both ways. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-26 16:07 Matthias van de Meent <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Matthias van de Meent @ 2023-07-26 16:07 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Wed, 26 Jul 2023 at 15:42, Peter Geoghegan <[email protected]> wrote: > > On Wed, Jul 26, 2023 at 5:29 AM Matthias van de Meent > <[email protected]> wrote: > > Considering that it caches/reuses the page across SAOP operations, can > > (or does) this also improve performance for index scans on the outer > > side of a join if the order of join columns matches the order of the > > index? > > It doesn't really cache leaf pages at all. What it does is advance the > array keys locally, while the original buffer lock is still held on > that same page. Hmm, then I had a mistaken understanding of what we do in _bt_readpage with _bt_saveitem. > > That is, I believe this caches (leaf) pages across scan keys, but can > > (or does) it also reuse these already-cached leaf pages across > > restarts of the index scan/across multiple index lookups in the same > > plan node, so that retrieval of nearby index values does not need to > > do an index traversal? > > I'm not sure what you mean. There is no reason why you need to do more > than one single descent of an index to scan many leaf pages using many > distinct sets of array keys. Obviously, this depends on being able to > observe that we really don't need to redescend the index to advance > the array keys, again and again. Note in particularly that this > usually works across leaf pages. In a NestedLoop(inner=seqscan, outer=indexscan), the index gets repeatedly scanned from the root, right? It seems that right now, we copy matching index entries into a local cache (that is deleted on amrescan), then we drop our locks and pins on the buffer, and then start returning values from our local cache (in _bt_saveitem). We could cache the last accessed leaf page across amrescan operations to reduce the number of index traversals needed when the join key of the left side is highly (but not necessarily strictly) correllated. The worst case overhead of this would be 2 _bt_compares (to check if the value is supposed to be fully located on the cached leaf page) plus one memcpy( , , BLCKSZ) in the previous loop. With some smart heuristics (e.g. page fill factor, number of distinct values, and whether we previously hit this same leaf page in the previous scan of this Node) we can probably also reduce this overhead to a minimum if the joined keys are not correllated, but accellerate the query significantly when we find out they are correllated. Of course, in the cases where we'd expect very few distinct join keys the planner would likely put a Memoize node above the index scan, but for mostly unique join keys I think this could save significant amounts of time, if only on buffer pinning and locking. I guess I'll try to code something up when I have the time, as it sounds not quite exactly related to your patch but an interesting improvement nonetheless. Kind regards, Matthias van de Meent ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-27 04:13 Peter Geoghegan <[email protected]> parent: Matthias van de Meent <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Peter Geoghegan @ 2023-07-27 04:13 UTC (permalink / raw) To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Wed, Jul 26, 2023 at 12:07 PM Matthias van de Meent <[email protected]> wrote: > We could cache the last accessed leaf page across amrescan operations > to reduce the number of index traversals needed when the join key of > the left side is highly (but not necessarily strictly) correllated. That sounds like block nested loop join. It's possible that that could reuse some infrastructure from this patch, but I'm not sure. In general, SAOP execution/MDAM performs "duplicate elimination before it reads the data" by sorting and deduplicating the arrays up front. While my patch sometimes elides a primitive index scan, primitive index scans are already disjuncts that are combined to create what can be considered one big index scan (that's how the planner and executor think of them). The patch takes that one step further by recognizing that it could quite literally be one big index scan in some cases (or fewer, larger scans, at least). It's a natural incremental improvement, as opposed to inventing a new kind of index scan. If anything the patch makes SAOP execution more similar to traditional index scans, especially when costing them. Like InnoDB style loose index scan (for DISTINCT and GROUP BY optimization), block nested loop join would require inventing a new type of index scan. Both of these other two optimizations involve the use of semantic information that spans multiple levels of abstraction. Loose scan requires duplicate elimination (that's the whole point), while IIUC block nested loop join needs to "simulate multiple inner index scans" by deliberately returning duplicates for each would-be inner index scan. These are specialized things. To be clear, I think that all of these ideas are reasonable. I just find it useful to classify these sorts of techniques according to whether or not the index AM API would have to change or not, and the general nature of any required changes. MDAM can do a lot of cool things without requiring any revisions to the index AM API, which should allow it to play nice with everything else (index path clause safety issues notwithstanding). -- Peter Geoghegan ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-27 11:59 Matthias van de Meent <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Matthias van de Meent @ 2023-07-27 11:59 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Wed, 26 Jul 2023 at 15:42, Peter Geoghegan <[email protected]> wrote: > > On Wed, Jul 26, 2023 at 5:29 AM Matthias van de Meent > > I'm not sure I understand. MDAM seems to work on an index level to > > return full ranges of values, while "skip scan" seems to try to allow > > systems to signal to the index to skip to some other index condition > > based on arbitrary cutoffs. This would usually be those of which the > > information is not stored in the index, such as "SELECT user_id FROM > > orders GROUP BY user_id HAVING COUNT(*) > 10", where the scan would go > > though the user_id index and skip to the next user_id value when it > > gets enough rows of a matching result (where "enough" is determined > > above the index AM's plan node, or otherwise is impossible to > > determine with only the scan key info in the index AM). I'm not sure > > how this could work without specifically adding skip scan-related > > index AM functionality, and I don't see how it fits in with this > > MDAM/SAOP system. > > I think of that as being quite a different thing. > > Basically, the patch that added that feature had to revise the index > AM API, in order to support a mode of operation where scans return > groupings rather than tuples. Whereas this patch requires none of > that. It makes affected index scans as similar as possible to > conventional index scans. Hmm, yes. I see now where my confusion started. You called it out in your first paragraph of the original mail, too, but that didn't help me then: The wiki does not distinguish "Index Skip Scans" and "Loose Index Scans", but these are not the same. In the one page on "Loose indexscan", it refers to MySQL's "loose index scan" documentation, which does handle groupings, and this was targeted with the previous, mislabeled, "Index skipscan" patchset. However, crucially, it also refers to other databases' Index Skip Scan documentation, which document and implement this approach of 'skipping to the next potential key range to get efficient non-prefix qual results', giving me a false impression that those two features are one and the same when they are not. It seems like I'll have to wait a bit longer for the functionality of Loose Index Scans. > > > [...] > > > > > > Thoughts? > > > > MDAM seems to require exponential storage for "scan key operations" > > for conditions on N columns (to be precise, the product of the number > > of distinct conditions on each column); e.g. an index on mytable > > (a,b,c,d,e,f,g,h) with conditions "a IN (1, 2) AND b IN (1, 2) AND ... > > AND h IN (1, 2)" would require 2^8 entries. > > Note that I haven't actually changed anything about the way that the > state machine generates new sets of single value predicates -- it's > still just cycling through each distinct set of array keys in the > patch. > > What you describe is a problem in theory, but I doubt that it's a > problem in practice. You don't actually have to materialize the > predicates up-front, or at all. Yes, that's why I asked: The MDAM paper's examples seem to materialize the full predicate up-front, which would require a product of all indexed columns' quals in size, so that materialization has a good chance to get really, really large. But if we're not doing that materialization upfront, then there is no issue with resource consumption (except CPU time, which can likely be improved with other methods) Kind regards, Matthias van de Meent Neon (https://neon.tech/) ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-27 13:59 Matthias van de Meent <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Matthias van de Meent @ 2023-07-27 13:59 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Thu, 27 Jul 2023 at 06:14, Peter Geoghegan <[email protected]> wrote: > > On Wed, Jul 26, 2023 at 12:07 PM Matthias van de Meent > <[email protected]> wrote: > > We could cache the last accessed leaf page across amrescan operations > > to reduce the number of index traversals needed when the join key of > > the left side is highly (but not necessarily strictly) correllated. > > That sounds like block nested loop join. It's possible that that could > reuse some infrastructure from this patch, but I'm not sure. My idea is not quite block nested loop join. It's more 'restart the index scan at the location the previous index scan ended, if heuristics say there's a good chance that might save us time'. I'd say it is comparable to the fast tree descent optimization that we have for endpoint queries, and comparable to this patch's scankey optimization, but across AM-level rescans instead of internal rescans. See also the attached prototype and loosely coded patch. It passes tests, but it might not be without bugs. The basic design of that patch is this: We keep track of how many times we've rescanned, and the end location of the index scan. If a new index scan hits the same page after _bt_search as the previous scan ended, we register that. Those two values - num_rescans and num_samepage - are used as heuristics for the following: If 50% or more of rescans hit the same page as the end location of the previous scan, we start saving the scan's end location's buffer into the BTScanOpaque, so that the next _bt_first can check whether that page might be the right leaf page, and if so, immediately go to that buffer instead of descending the tree - saving one tree descent in the process. Further optimizations of this mechanism could easily be implemented by e.g. only copying the min/max index tuples instead of the full index page, reducing the overhead at scan end. Kind regards, Matthias van de Meent Neon (https://neon.tech) Attachments: [application/octet-stream] v1-0001-Cache-btree-scan-end-page-across-rescans-in-the-s.patch.cfbot-ignore (8.6K, ../../CAEze2WhRef3bnfRbRbeYAnemxrixM-g1p3tKzOHHb6hP2Z4=5A@mail.gmail.com/2-v1-0001-Cache-btree-scan-end-page-across-rescans-in-the-s.patch.cfbot-ignore) download ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-27 14:00 Peter Geoghegan <[email protected]> parent: Matthias van de Meent <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Peter Geoghegan @ 2023-07-27 14:00 UTC (permalink / raw) To: Matthias van de Meent <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Thu, Jul 27, 2023 at 7:59 AM Matthias van de Meent <[email protected]> wrote: > > Basically, the patch that added that feature had to revise the index > > AM API, in order to support a mode of operation where scans return > > groupings rather than tuples. Whereas this patch requires none of > > that. It makes affected index scans as similar as possible to > > conventional index scans. > > Hmm, yes. I see now where my confusion started. You called it out in > your first paragraph of the original mail, too, but that didn't help > me then: > > The wiki does not distinguish "Index Skip Scans" and "Loose Index > Scans", but these are not the same. A lot of people (myself included) were confused on this point for quite a while. To make matters even more confusing, one of the really compelling cases for the MDAM design is scans that feed into GroupAggregates -- preserving index sort order for naturally big index scans will tend to enable it. One of my examples from the start of this thread showed just that. (It just so happened that that example was faster because of all the "skipping" that nbtree *wasn't* doing with the patch.) > Yes, that's why I asked: The MDAM paper's examples seem to materialize > the full predicate up-front, which would require a product of all > indexed columns' quals in size, so that materialization has a good > chance to get really, really large. But if we're not doing that > materialization upfront, then there is no issue with resource > consumption (except CPU time, which can likely be improved with other > methods) I get why you asked. I might have asked the same question. As I said, the MDAM paper has *surprisingly* little to say about B-Tree executor stuff -- it's almost all just describing the preprocessing/transformation process. It seems as if optimizations like the one from my patch were considered too obvious to talk about and/or out of scope by the authors. Thinking about the MDAM paper like that was what made everything fall into place for me. Remember, "missing key predicates" isn't all that special. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan @ 2023-07-28 11:11 Matthias van de Meent <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Matthias van de Meent @ 2023-07-28 11:11 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; Jeff Davis <[email protected]>; benoit <[email protected]> On Thu, 27 Jul 2023 at 16:01, Peter Geoghegan <[email protected]> wrote: > > On Thu, Jul 27, 2023 at 7:59 AM Matthias van de Meent > <[email protected]> wrote: > > > Basically, the patch that added that feature had to revise the index > > > AM API, in order to support a mode of operation where scans return > > > groupings rather than tuples. Whereas this patch requires none of > > > that. It makes affected index scans as similar as possible to > > > conventional index scans. > > > > Hmm, yes. I see now where my confusion started. You called it out in > > your first paragraph of the original mail, too, but that didn't help > > me then: > > > > The wiki does not distinguish "Index Skip Scans" and "Loose Index > > Scans", but these are not the same. > > A lot of people (myself included) were confused on this point for > quite a while. I've taken the liberty to update the "Loose indexscan" wiki page [0], adding detail that Loose indexscans are distinct from Skip scans, and showing some high-level distinguishing properties. I also split the TODO entry for `` "loose" or "skip" scan `` into two, and added links to the relevant recent threads so that it's clear these are different (and that some previous efforts may have had a confusing name). I hope this will reduce the chance of future confusion between the two different approaches to improving index scan performance. Kind regards, Matthias van de Meent Neon (https://neon.tech) [0]: https://wiki.postgresql.org/wiki/Loose_indexscan ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-28 11:11 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-08-12 20:27 [PATCH 1/1] Fix printing last progress report line in client programs. Heikki Linnakangas <[email protected]> 2023-07-26 13:41 Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Peter Geoghegan <[email protected]> 2023-07-26 16:07 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Matthias van de Meent <[email protected]> 2023-07-27 04:13 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Peter Geoghegan <[email protected]> 2023-07-27 13:59 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Matthias van de Meent <[email protected]> 2023-07-27 11:59 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Matthias van de Meent <[email protected]> 2023-07-27 14:00 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Peter Geoghegan <[email protected]> 2023-07-28 11:11 ` Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan Matthias van de Meent <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox