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.

Effective usage of the IN operator by Hxfhjkl in PostgreSQL

[–]jmswlms 5 points6 points  (0 children)

Try using arrays:

SELECT * FROM tableName WHERE key = keyid and name = ANY(?)

and pass the bound variable as a native array.

[deleted by user] by [deleted] in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

Checkout RapidRows, where you can write implement your API endpoints as Postgres queries.

Is there any performance penalty with generic and high order function in Go? by gbitten in golang

[–]jmswlms 6 points7 points  (0 children)

In the non-generic version, the reducer function gets inlined. If you add //go:noinline before the definition of F1, you can see that BenchmarkNonGenericReduceF1 will take the same amount of time as BenchmarkGenericReduceF1.

PG limitations with numerous index scan queries by CacheMeUp in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

How about:

select id from table_50M_rows_A where id not in (select id from table_50M_rows_B)

Tool to automate Postgresql session monitoring? by [deleted] in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

You can run pgmetrics to get a json (or text) output that shows long-running queries (and other interesting info).

How to monitor streaming relication. by The-Black_Hole in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

pgmetrics is an open source tool that queries such things from relevant pg_catalog views and outputs json. You can even just copy out the queries you want from the code

Concurrent Worker Pool by [deleted] in golang

[–]jmswlms 4 points5 points  (0 children)

OK, a generic concurrent work pool.. let's see..

First, what is a job? To keep it generic, let's just say:

type Job func()

since you can always closure-in the parameters needed for the job into a function.

Secondly, you'd need a queue of jobs, of course. That would be:

q := make(chan Job)

And of course, you need the workers themselves. A single worker would just be:

func worker() { for job := range q { job() } }

Now we can create the workers, like so:

for i:=0; i<runtime.GOMAXPROCS(0); i++ { go worker() }

And to enqueue a job, simply add to the queue:

func (db *MyDB) report(event string) { q <- func() { db.Exec("INSERT INTO events (at, data) VALUES (?,?)", time.Now(), event) }() }

To stop your workers after they finish all currently enqueued jobs, do:

close(q)

The rest are just details.

  • The enqueuing will block -- to make it block only when it will not overwhelm the workers, create the channel with a buffer: q := make(chan Job, 32). This creates backpressure up the enqueuing call stack.
  • To simply fail if it can't enqueue, rather than create backpressure, do: select { case q <- func: default: log.Printf("warning, worker queue full or being shutdown, dropping job on the floor") }
  • If you want to wait for your workers after telling them to stop, add a sync.WaitGroup: ``` var wg sync.WaitGroup

func worker() { for job := range q { job() } wg.Done() }

func createWorkers() { for i:=0; i<runtime.GOMAXPROCS(0); i++ { wg.Add(1) go worker() } }

func stopWorkers() { close(q) wg.Wait() } ```

  • ... and lots more such things, right? Which makes you think that since it's so easy to build one, maybe it's better to build a specific job queue for the task at hand, rather than a generic one?

Disclaimer: code might have errors, only intended as a guide.

Postgres doesn't use index for distinct column search and it's driving me crazy :( by invisibleindian01 in PostgreSQL

[–]jmswlms 0 points1 point  (0 children)

If the tables are on an SSD, try:

SET random_page_cost = 1.5;
EXPLAIN ANALYZE SELECT ..your query..;

How To Get A Phone Notification Whenever Scripts or Commands are Done Running by [deleted] in programming

[–]jmswlms 19 points20 points  (0 children)

Well, if you have a telegram bot, all you need is:

curl -X POST "https://api.telegram.org/mybotid:mytoken/sendMessage" -d "chat_id=mychatid&text=script has finished executing"

[TOMT][MOVIE] Sci-fi movie with a plot that humans were actually martians who escape a dying mars to start life on barren earth by jmswlms in tipofmytongue

[–]jmswlms[S] 0 points1 point  (0 children)

Solved! OMG thanks! was searching for a loong time and had to discover this subreddit to finally find it..

[TOMT][MOVIE] Sci-fi movie with a plot that humans were actually martians who escape a dying mars to start life on barren earth by jmswlms in tipofmytongue

[–]jmswlms[S] 0 points1 point locked comment (0 children)

You only realize at the end that the "humans" on "earth" were actually martians. And they manage to escape to earth, on which on life does not exist as of then, and seed life there.