search_plan_tree(): handling of non-leaf CustomScanState nodes causes segfault

Hi hackers, While working with cursors that reference plans with CustomScanStates nodes, I encountered a segfault which originates from search_plan_tree(). The query plan is the result of a simple SELECT statement into which I inject a Custom Scan node at the root to do some post-processing b

Re: search_plan_tree(): handling of non-leaf CustomScanState nodes causes segfault

Hi, On 18.01.21 19:46, Tom Lane wrote: David Geier writes: search_plan_tree() assumes that CustomScanState::ScanState::ss_currentRelation is never NULL. In my understanding that only holds for CustomScanState nodes which are at the bottom of the plan and actually read from a relation

Re: search_plan_tree(): handling of non-leaf CustomScanState nodes causes segfault

Hi, On 18.01.21 23:42, Tom Lane wrote: David Geier writes: On 18.01.21 19:46, Tom Lane wrote: Hm. I agree that we shouldn't simply assume that ss_currentRelation isn't null. However, we cannot make search_plan_tree() descend through non-leaf CustomScan nodes, because we don&#

Re: postgres_fdw fails to see that array type belongs to extension

Hi, I realized that ALTER EXTENSION foo ADD TYPE _bar does pretty much the same via ExecAlterExtensionContentsStmt(). So the code in the patch seems fine. On 1/8/24 12:21, David Geier wrote: The attached patch just adds a 2nd dependency between the array type and the extension, using

Re: pg_stat_statements and "IN" conditions

hat exactly do you mean by "more flexible"? -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

cessary at the call site? If no one objects I would also re-register this patch in the commit fest. [1] https://vmware.com/content/dam/digitalmarketing/vmware/en/pdf/techpaper/Timekeeping-In-VirtualMachines.pdf (page 11 "Virtual TSC") -- David Geier (ServiceNow) From 321d00ae5dd1bcffc

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

ities. That's good and bad. Bad to do the development and good to test the implementation on more virtualized setups; given that I also encountered "interesting" behavior on VMWare (see my previous mails). On Mon, Jan 2, 2023 at 5:28 AM David Geier wrote: The INSTR_TIME_GET_

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

eturned 1 exit status [1] - https://cirrus-ci.com/task/5375312565895168 Regards, Vignesh I fixed the compilation error on CFBot. I missed adding instr_time.c to the Meson makefile. New patch set attached. -- David Geier (ServiceNow) From be18633d4735f680c7910fcb4e8ac90c4eada131 Mon Sep 17 00:00:00

Re: Sampling-based timing for EXPLAIN ANALYZE

[1] https://www.postgresql.org/message-id/flat/20200612232810.f46nbqkdhbutzqdg%40alap3.anarazel.de -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

e timing source in the EXPLAIN ANALYZE output. It's a good tradeoff between inspectability and effort, given that RDTSC should always be better to use. If there are no objections I go this way. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

On 1/16/23 18:37, Andres Freund wrote: Hi, On 2023-01-02 14:28:20 +0100, David Geier wrote: I'm doubtful this is worth the complexity it incurs. By the time we convert out of the instr_time format, the times shouldn't be small enough that the accuracy is affected much. I don

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

Hi, @Andres: will you take care of these changes and provide me with an updated patch set so I can rebase the RDTSC changes? Otherwise, I can also apply Tom suggestions to your patch set and send out the complete patch set. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

's patches should be rebased over these anyway? That's alright. Though, I would hope we attempt to bring your patch set as well as the RDTSC patch set in. -- David Geier (ServiceNow)

Fix assertion in autovacuum worker

arted and hence LWLockReleaseAll() doesn't run before pgstat_shutdown_hook() is called. See attached patch for an attempt to fix this issue. -- David Geier (ServiceNow) From 5580e3680b2211235e4bc2b5dcbfe6b4f5b8eee5 Mon Sep 17 00:00:00 2001 From: David Geier Date: Tue, 28 Nov 2023 18:52:46 +01

postgres_fdw fails to see that array type belongs to extension

type is only dependent on our extension via the custom data type in two steps which postgres_fdw doesn't see. Therefore, postgres_fdw doesn't allow for push-down of the IN. Thoughts? -- David Geier (ServiceNow)

Re: postgres_fdw fails to see that array type belongs to extension

sions in contrib use test extension in their own tests? It looks like postgres_fdw doesn't test any of the shippability logic. -- David Geier (ServiceNow) From de23a4e9f1b0620a5204594139568cdcb3d57885 Mon Sep 17 00:00:00 2001 From: David Geier Date: Mon, 8 Jan 2024 10:58:21 +0100 Subject:

Re: Eliminate redundant tuple visibility check in vacuum

HeapTupleSatisfiesVacuum() again on every tuple in the page. It also gets rid of the retry loop in lazy_scan_prune(). How did you test this change? Could you measure any performance difference? If so could you provide your test case? -- David Geier (ServiceNow)

Re: Eliminate redundant tuple visibility check in vacuum

that case now not be ignored? -- David Geier (ServiceNow)

Re: Eliminate redundant tuple visibility check in vacuum

planations. Please update the comment to document the corner case and how we handle it. -- David Geier (ServiceNow)

Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE

bly needed memory would have to get initialized, the remaining unused memory would remain untouched to not waste cycles. My first preference is the new option (4). My second preference is option (1). What's your take? -- David Geier (ServiceNow)

Re: how to do profile for pg?

Hi, This talk from Andres seems to have some relevant information for you: https://www.youtube.com/watch?v=HghP4D72Noc -- David Geier (ServiceNow)

Optimize join selectivity estimation by not reading MCV stats for unique join attributes

s for default_statistics_target. That's because get_attstatsslot() deconstructs the array holding the MCV. The size of that array depends on default_statistics_target. Thanks for your consideration! -- David Geier (ServiceNow) From 7a8176b9eb9dd9982662d83b86996c5402378674 Mon Sep 17 00:00:00 2001 From: D

Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes

the above query five times gave me average runtimes of: - 0.62 ms without the patch and - 0.48 ms with the patch. -- David Geier (ServiceNow)

Re: Assertion failure with barriers in parallel hash join

Hi Thomas, Can we make progress with this patch in the current commit fest, or discuss what is still missing to bring this in? Thanks! -- David Geier (ServiceNow) On 6/6/22 17:01, David Geier wrote: Hi Thomas, Correct. We're running with disabled parallel leader participation and we

Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes

han you are from some wasted cycles here. Sounds reasonable. Attached is v2 of the patch. This is basically Tom's version plus a comment for the flags of get_attstatslot() as suggested by Richard. I couldn't come up with any reasonable way of writing an automated t

Re: Assertion failure with barriers in parallel hash join

Thanks! Please let me know if I can help out, e.g. with re-testing. -- David Geier (ServiceNow) On 11/17/22 08:28, Thomas Munro wrote: On Thu, Nov 17, 2022 at 8:01 PM David Geier wrote: Can we make progress with this patch in the current commit fest, or discuss what is still missing to

Re: CREATE UNLOGGED TABLE seq faults when debug_discard_caches=1

thing passes. - version 14.5: fails in create_index, create_index_spgist, create_view. So the buggy code path is at least covered by the tests. But it seems like we could have found it earlier by regularly running with debug_discard_caches=1. -- David Geier (ServiceNow) On 11/17/22 18:51, Tom

Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes

when the pg_stats entry is not in syscache yet. But that seems awfully implementation specific. So no test provided. -- David Geier (ServiceNow) From 5c5c0fb9dd99e79daaa015984c9dda22e4ccda17 Mon Sep 17 00:00:00 2001 From: David Geier Date: Fri, 18 Nov 2022 09:35:08 +0100 Subject: [PATCH] Don&#

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

urce as GUC? That way the user can switch back to a working clock source in case we miss a detail around activating or reading the TSC. I'm happy to update the patches accordingly. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

I missed attaching the patches. -- David Geier (ServiceNow) From f4e962729ca605498d0c8bfc97d0f42d68a0df06 Mon Sep 17 00:00:00 2001 From: David Geier Date: Thu, 17 Nov 2022 10:22:01 +0100 Subject: [PATCH 1/2] WIP: Change instr_time to just store nanoseconds, that's cheaper. --- src/in

Aggregate node doesn't include cost for sorting

ning? As soon as there's also a GROUP BY in the query, a Sort node occurs in the plan. This seems inconsistent. -- David Geier (ServiceNow)

Re: Aggregate node doesn't include cost for sorting

n the join search once per required order, which is one of the slowest parts of planning. Right now, you could probably make that work by just writing the SQL to have a subquery per sort requirement. Thanks for the explanation! -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

On 1/18/23 13:52, David Geier wrote: On 1/16/23 21:39, Pavel Stehule wrote: po 16. 1. 2023 v 21:34 odesílatel Tomas Vondra napsal:     Hi,     there's minor bitrot in the Mkvcbuild.pm change, making cfbot unhappy.     As for the patch, I don't have much comments. I'm won

Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE

d right away also add tests for EXPLAIN ANALYZE including other parallel nodes. Thank you for your feedback. -- David Geier (ServiceNow) From b2c84fb16e9521d6cfadb0c069e27a213e8e8471 Mon Sep 17 00:00:00 2001 From: David Geier Date: Tue, 8 Nov 2022 19:40:31 +0100 Subject: [PATCH v1] Parallel Bit

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

he moment being. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

h sources of counters. I haven't yet looked into pg_test_timing. I'll do that while including your patches into the new patch set. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

n the CF entry (and thus cfbot) makes sense again... I'll include them in my new patch set and also have a careful look at them. -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

he code a little more. - Add more documentation and do more testing around the calls to CPUID. - Profiling and optimizing the code. A quick test showed about 10% improvement over master with TIMING ON vs TIMING OFF, when using the test-case from Andres' e-mail that started this thr

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

er to my latest e-mail which also includes the patch to have some sort of summary of where we are in a single place. -- David Geier (ServiceNow)

Re: pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

-fde4-e12f0661dbf0%40postgrespro.ru -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

* NS_PER_S. The rest looks good to me. The rebased patches are part of the patch set I sent out yesterday in reply to another mail in this thread. -- David Geier (ServiceNow)

Re: Lazy JIT IR code generation to increase JIT speed with partitions

just super slow, especially for big bitcode files like numeric.bc. I haven't investigated why that is and if we can do something about it. I also don't plan to do so for the moment being. For reference, I attached the patch which only contains the caching code. It's the variant

Re: Lazy JIT IR code generation to increase JIT speed with partitions

or helping with this effort. -- David Geier (ServiceNow)

Performance issues with parallelism and LIMIT

[1] https://www.postgresql.org/message-id/flat/CAFiTN-tVXqn_OG7tHNeSkBbN%2BiiCZTiQ83uakax43y1sQb2OBA%40mail.gmail.com -- David Geier (ServiceNow)

Re: pg_stat_statements and "IN" conditions

        return false;     // All elements are of type Const         *firstConst = linitial_node(Const, elements);         *lastConst = llast_node(Const, elements);         return true; -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

e cannot obtain the frequency via leaf 0x16. Beyond that I reviewed the first two patches a while ago in [1]. I hope we can progress with them to further reduce the size of this patch set. [1] https://www.postgresql.org/message-id/3ac157f7-085d-e071-45fc-b87cd306360c%40gmail.com -- Dav

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

Hi! On 2/14/23 12:11, David Geier wrote: Hi, I think I fixed the compilation errors. It was due to a few variables being declared under #if defined(__x86_64__) && defined(__linux__) while being used also under non x86 Linux. I also removed again the code to obtain the TSC frequen

Re: pg_stat_statements and "IN" conditions

nt) || (is_coercion(element) && is_const(element->child)) -- David Geier (ServiceNow)

Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?

Hi! On 2/14/23 13:48, David Geier wrote: It still fails. I'll get Cirrus-CI working on my own Github fork so I can make sure it really compiles on all platforms before I submit a new version. It took some time until Cirrus CI allowed me to run tests against my new GitHub account (th

Re: Performance issues with parallelism and LIMIT

Hi, On 2/8/23 11:42, Tomas Vondra wrote: On 2/1/23 14:41, David Geier wrote: Yeah, this is a pretty annoying regression. We already can hit poor behavior when matching rows are not distributed uniformly in the tables (which is what LIMIT costing assumes), and this makes it more likely to hit

Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE

Hi, On 1/20/23 09:34, David Geier wrote: EXPLAIN ANALYZE for parallel Bitmap Heap Scans currently only reports the number of heap blocks processed by the leader. It's missing the per-worker stats. The attached patch adds that functionality in the spirit of e.g. Sort or Memoize. Here

Add explicit casts in four places to simplehash.h

about, while not being strictly necessary for PostgreSQL itself, also adding such casts to simplehash.h so that it can be used in code where -Wc++-compat is enabled? Attached is a small patch that adds the aforementioned casts. Thanks for your consideration! -- David Geier (ServiceNow) From

Re: Add explicit casts in four places to simplehash.h

seful. -- David Geier (ServiceNow)

Re: WIP Patch: Precalculate stable functions, infrastructure v1

tions, infrastructure v1 (Marina, 2017), https://www.postgresql.org/message-id/flat/da87bb6a014e029176a04f6e50033cfb%40postgrespro.ru -- David Geier (ServiceNow) On Mon, 23 May 2022 at 17:06, Andres Freund wrote: > On 2018-11-29 18:00:15 +0100, Dmitry Dolgov wrote: > > > On Tue, Oct 2, 2018 a

Re: Assertion failure with barriers in parallel hash join

60, in ExecHashJoinImpl Source "/opt/src/backend/executor/nodeHashjoin.c", line 1132, in ExecParallelHashJoinNewBatch [0x67a89b] #5 | Source "/opt/src/backend/storage/ipc/barrier.c", line 242, in BarrierAttach Source "/opt/src/include/storage/s_lock.h", line 228, in tas [0x7c2a1b

Re: Assertion failure with barriers in parallel hash join

00:06, Thomas Munro wrote: > On Thu, Jun 2, 2022 at 9:31 PM David Geier wrote: > > We recently encountered the same bug in the field. Oleksii Kozlov > managed to come up with reproduction steps, which reliably trigger it. > Interestingly, the bug does not only manifest as failing asse

Re: Lazy JIT IR code generation to increase JIT speed with partitions

ck-world' all tests passed. The attached patch currently does not yet have a case distinction for LLVM 13. But that would be straightforward to add. Thanks for your consideration. -- David Geier (ServiceNow) [1] https://lists.llvm.org/pipermail/llvm-dev/2018-March/122111.html [2] https:

Re: Lazy JIT IR code generation to increase JIT speed with partitions

master to then compare them with the patched version? Also, which LLVM version did you run with? I'm currently running with LLVM 13. Thanks! -- David Geier (ServiceNow) On Mon, Jun 27, 2022 at 5:37 PM Alvaro Herrera wrote: > On 2021-Jan-18, Luc Vlaming wrote: > > > I would

Improving scalability of Parallel Bitmap Heap/Index Scan

processes acquire chunks of TIDs / page bits to reduce locking. Is there interest in patches improving on the above mentioned shortcomings? If so, which options do you deem best? -- David Geier (ServiceNow) -- 2 workers Finalize Aggregate (actual time=15228.937..15321.356 rows=1 loops=1)

Re: Lazy JIT IR code generation to increase JIT speed with partitions

On Mon, Jul 4, 2022 at 10:32 PM Andres Freund wrote: > Hi, > > On 2022-06-27 16:55:55 +0200, David Geier wrote: > > Indeed, the total JIT time increases the more modules are used. The > reason > > for this to happen is that the inlining pass loads and deserializes all >

Re: Lazy JIT IR code generation to increase JIT speed with partitions

. The penalty for the best case seems low though, because (1) the overhead is low in absolute terms, and (2) also if the entire plan truly benefits from jitting, spending sub-ms more per node seems neglectable because there is anyways going to be significant time spent. -- David Geier

Reducing planning time on tables with many indexes

| 80,121 | 0.046 ms | 3.0x| 6.3x 16 | 152,632 | 0.051 ms | 4.9x| 8.4x 32 | 301,359 | 0.052 ms | 11.4x| 32.3x 64 | 525,115 | 0.062 ms | 21.6x| 40.0x We are h

[PoC] Reducing planning time on tables with many indexes

| 80,121 | 0.046 ms | 3.0x| 6.3x 16 | 152,632 | 0.051 ms | 4.9x| 8.4x 32 | 301,359 | 0.052 ms | 11.4x| 32.3x 64 | 525,115 | 0.062 ms | 21.6x| 40.0x We are h

Re: [PoC] Reducing planning time on tables with many indexes

Sorry, by accident I sent this one out twice. -- David Geier (ServiceNow) On Wed, Jul 27, 2022 at 2:42 PM David Geier wrote: > Hi hackers, > > We came across a slowdown in planning, where queries use tables with many > indexes. In setups with wide tables it is not uncommon to have

Re: Reducing planning time on tables with many indexes

Hi Tom, On Wed, Jul 27, 2022 at 7:15 PM Tom Lane wrote: > I wrote: > > Unfortunately, as things stand today, the planner needs more than the > > right to look at the indexes' schemas, because it makes physical accesses > > to btree indexes to find out their tree height (and I think there are > s

Re: Reducing planning time on tables with many indexes

Hi Tom, On Wed, Jul 27, 2022 at 6:39 PM Tom Lane wrote: > David Geier writes: > > We tracked down the root cause of this slowdown to lock contention in > > 'get_relation_info()'. The index lock of every single index of every > single > > table used in that que

Re: Let's make PostgreSQL multi-threaded

red buffers sparsely, memory is wasted for the remaining, unused range inside the huge page. -- David Geier (ServiceNow)

Re: Let's make PostgreSQL multi-threaded

Hi, On 8/11/23 14:05, Merlin Moncure wrote: On Thu, Jul 27, 2023 at 8:28 AM David Geier wrote: Hi, On 6/7/23 23:37, Andres Freund wrote: > I think we're starting to hit quite a few limits related to the process model, > particularly on bigger machines. The

Re: Reducing planning time on tables with many indexes

On 8/1/22 15:33, David Geier wrote: Hi Tom, On Wed, Jul 27, 2022 at 7:15 PM Tom Lane wrote: I wrote: > Unfortunately, as things stand today, the planner needs more than the > right to look at the indexes' schemas, because it makes physical accesses > to