New answers tagged postgresql-10
1
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
x,
case when random()<0.25 then NULL else floor(random()*10000)::int ...
0
The culprit was even with VACUUM FULL ANALYZE, query planner was still not using indexes in many cases.
Solved by forcing SET enable_seqscan = OFF;, after few autovacuums, it re-adjusted.
1
The output of pg_upgrade includes this:
...
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
...
We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)
-1
Use this "localedef -f UTF-8 -i en_US en_US.UTF-8" and reboot.
0
Use an array of values, bear in mind both values must be included in favoriteColors
select
favourites->'favouriteCity'
from
t
where
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 @> ...
0
You can use the contains all ?& operator with arrays:
select *
from r_fav
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
1
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 ...
1
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.
select
b_d.b_id,
jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d....
0
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.
0
Even tho PG is case sensitive in other cases, it requires lowercase usernames. Using a lower case name during login fixed the issue.
Top 50 recent answers are included
