While I can see some strange planner behavior (where indexes with similar actual performance have dramatically different estimates), I can't replicate you main complaint that none of the indexes are not used.
If I create data which is ordered randomly:
create table test as select
case when random()<0.25 then NULL else floor(random()*10000)::int ...
The output of pg_upgrade includes this:
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)
Use an array of values, bear in mind both values must be included in favoriteColors
favourites->'favoriteColors' @> '["Blue","Red"]'::jsonb;
| ?column? |
| :------- |
| "Boston" |
Let me add this comment of jjanes:
You could also put the key name on the other side of the @> ...
You can use the contains all ?& operator with arrays:
where favorites -> 'favoriteColors' ?| array['Blue', 'Red']
That will return rows at least one of the colors exist in the array. If want those where all colors are contained, use the operator ?& instead
I discovered that the wait_event_type: Extension was referring to it getting locked up in a query passed to the foreign data wrapper. However, the problem does not seem to be the Postgres_FDW, but rather using Language SQL to write my function and not using LANGUAGE plpgsql. I recalled a post either here, on StackOverflow, or another forum which mentioned ...
Instead of looking on the json functions docs page, look instead on the aggregate functions docs page. jsonb_object_agg() will aggregate the object without an additional level of nesting as seen in this demo.
jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
join d on d.id = b_d.d_id
join c on c.id = d....
As stated By Laurenz, you must install PostGIS in the same schema in both databases.
Afterward, it is possible to migrate postgis extensions doing the following : https://www.postgis.net/2017/11/07/tip-move-postgis-schema/
The migration can be done in an intermediate DB if need be.