New answers tagged

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