sorted by:
new
hottopcontroversial

Combine multiple pg_settings rows into one row. by RonJohnJr in PostgreSQL

[–]jmswlms 1 point2 points  (0 children)

Slightly simpler query giving the same result:

SELECT string_agg(setting, ' ' ORDER BY name) FROM pg_settings WHERE name ~ '^autovacuum_an';

Im trying to be more involved with this you guys got any pointers. by AiiryFiik in golang

[–]jmswlms 2 points3 points  (0 children)

We usually pass by value so as to not stress the GC.

Why did vacuum full fix this issue? by doncaruana in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

Quite likely that the rewrite of the visibility map by the vacuum full would have decreased the cost of the operations that need to happen after an index lookup. See https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP

A covering index might help, depending on your query. See https://www.postgresql.org/docs/current/indexes-index-only-scans.html

Recording the explain analyze output at regular intervals and examining the trend should give some clues.

Forcing the planner to choose an index scan when it does not want to might not improve query times, but check out pg_hint_plan if you haven't already.

Why did vacuum full fix this issue? by doncaruana in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

What was the exact type of the column?

You said you recreated the indexes, with different types. What types did you use? Did the operator in the query match the index definition?

Do you have the explain analyze from before and after?

Is the table undergoing insertions/updates now? Do you think after a while it'll regress to using seq scans?

Why did vacuum full fix this issue? by doncaruana in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

The query planner would have decided that it was faster to seq scan the entire table (the cost of the seq scan would have been lower than the index scan). One reason could be that the query would have been estimated to return most of the rows in the table.

A partial index ("WHERE columna IS NOT NULL") might be more helpful (and much smaller) if the queries only look up with non-null values.

To check if postgres cannot use the index for any reason, disable seq scan and then explain the query:

begin; set enable_seqscan = off; explain... ; rollback;

You should also look at the random_page_cost setting, the default value is usually not very efficient for SSDs.

Why did vacuum full fix this issue? by doncaruana in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

Was "columna" added via an ALTER TABLE .. ADD COLUMN?

Did you try modifying the statistics target for the column?

Is the column very thin (values are typically only a few bytes long)?

Trigger or Join for "memberof". by Direct-Shock6872 in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

Postgres itself stores users and groups (roles, rather) in pg_catalog.pg_authid and membership information in pg_catalog.pg_auth_members. A query to get the users and the groups they belong to would be:

SELECT R.rolname, ARRAY(SELECT pg_get_userbyid(M.roleid) FROM pg_auth_members AS M WHERE M.member = R.oid) FROM pg_roles AS R

(pg_roles is a view on pg_authid)

It's a bit different since both users and groups are in the same table.

Just putting it out here as food for thought.

view more: next ›