materialize | Materialize | User Interface library

If you want to make a registration system I recommend php

First up: the two queries are not equivalent!

The json_value query gets the first entries in the DataRecord and ErrorRecord arrays. With json_table the database generates a row for each element in the array.

I see no join between jtrequest and jtresponse. So the query is generating the Cartesian product of these arrays. i.e. it's creating a row for every element from the first array combined with every element from the second for each document.

The rows/bytes/time columns are all estimates. The optimizer thinks this is how many rows/size data/query duration based on the table stats.

The top line in the plan is what's (estimated) the query will return. So for json_table, it's estimating:

• 350G => 350 billion rows
• 260T => 260 terabytes of data
• 20:42:27 => 20+ hours of runtime

These figures could be wrong for many reasons, but even if they're over by a factor of 1000x you're still looking at huge amounts of data.

I think you need to figure out the purpose of the original query - in particular why it's generating the Cartesian product of the two arrays. This quickly increases the data volumes.

What you need is to use TRUNC() function while creating a view such as

Queries using OR cannot use index (as efficiently). I suggest you try the following:

I think this fiddle fairly represents your situation.

Two different things could be going on to make this run super-slow.

First, it looks like your update query does a full table scan of a 90-megarow table. That's a lot of data.

Creating an index on table_A might help expedite the finding of eligible rows in table_A.

Two approaches come to mind:

1. Create a secondary table with (number, id) columns. Add a trigger so that whenever a duplicate row is about to be inserted into my_table, it is also inserted into this secondary table. That way you'll have the data you need in the secondary table as soon as it comes in, and it won't take up too much space unless you have a ton of these duplicates.

2. Add a new column to my_table, perhaps a timestamp, to differentiate the duplicates. Add a unique constraint to my_table over the (number, id) columns where the new column is null. Then, you can change your insert to include an ON CONFLICT clause, so that if a duplicate is being inserted you set its timestamp to now. When you want to search for duplicates, you can then just query using the new column.

Ah, so the problem here is that, while you call t1 a "2d tuple", it is really not; it is a nested tuple, a tuple-of-tuples, and is thus not really comparable to your 2d array (which really is a two-dimensional object and not just an array-of-arrays).

If you want to add a two-dimensional array to an immutable object that can be stack-allocated like a Tuple while being truly two-dimensional, then you can use the StaticArrays.jl package, which provides the immutable SArray type:

As per comments, it turns out that another ignore rule was overriding the desired un-ignore rule. The key diagnostic was to use:

Two options:

1. Make the role the owner of the database
2. Grant the role the specific permissions you want it to have