There is no way around unnesting the array for every row (the drawbacks of de-normalizing data). But you don't need a cross join, an EXISTS condition should work just fine.
from demo d
where exists (select *
jsonb_each(d.rawjson) as r(jdoc)
where (jdoc ->> 'a')::int < 2
and (jdoc ->> 'b')::...
You can try using the --snapshot method (https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/), but it might be a bit more complicated than you'd like.
My personal recommendation is to create a cluster-wide backup with pg_basebackup (or a third-party app that basically does the same thing, like barman, pg_backrest, etc.)
Not sure whether it answers your question, but you don't need to reference scans_minmax twice to get the result you want:
, case when "Aggregation" = 'MIN' then ok end as min_ok
, case when "Aggregation" = 'MAX' then ok end as max_ok
Now you have a table with 8 columns, 3 in each row is null. ...
This is expected behaviour: the view source is simply substituted for its name by the query rewrite, then analysed and executed as usual.
To tell the query compiler that you want to process the view only once you'd use a common table expression (CTE):
WITH scans_minmax (source, "Ok", "NotOk", "TotalCount", "Aggregation") AS (
There's almost no deletion taking place on the foos table, so the bloat is non-existent.
You don't need deletions to get bloat. updates will do so as well. A freshly made index on 10,000,000 ints gives me a size of 214MB, so you do have bloat (or are using weird hardware, perhaps). You can't detect bloat just by thinking about to. Rebuild the index and ...
Answer from comments by a-horse-with-no-name:
The manual does explain what those attributes are:
Currently, the only defined per-attribute options are n_distinct and
n_distinct_inherited, which override the number-of-distinct-values
estimates made by subsequent ANALYZE operations.
If you could introduce your own attributes the ...
Usually the most portable way of doing this is to have your own metadata table, something like:
create table meta(
table_name text not null,
column_name text not null,
attribute_name text not null,
attribute_value text not null,
primary key (table_name, column_name, attribute_name)
This approach works with any database
Access to metadata is ...
I'm going thru this issue myself, so one easy thing you can do is change the password of asd. That can be done by running
ALTER USER asd WITH PASSWORD 'new_password';
After that, I tried the following, which was provided by AWS Support and even took me a while to realize which commands should be executed by which role.
-- logged in as postgres, grant the ...