New answers tagged

0

The technique for such requirements is called cursor. You start a transaction and use your API's functions or the SQL command DECLARE to create a cursor. Then the query is only executed once, but you can FETCH the results in chunks.


0

Have you ever tested something similar in production? As a general rule, I don't test things in production if I can avoid it. I have certainly tested them in test, however. Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the ...


0

There are two approaches: create an index on the array: CREATE INDEX ON items USING gin (tags); That allows the database to quickly find the matching rows, but then it has to perform a top-n sort. create a B-tree index on created_at: CREATE INDEX ON items (created_at); That will allow the database to avoid the sort, but it has to scan and discard the ...


0

Generate the current explain plan using explain select * from table_name; Create a gin index on tags column and btree index on created at column. Generate the new query/explain plan post index creation to notice the cost difference and execution times.


1

Unless you have insert triggers that do complex stuff, an INSERT is probably bottlenecked by either CPU or I/O. Use vmstat 1 to determine what is the case: High CPU utilization is obvious, high I/O load manifests as iowait% at 10 or higher. This assumes that you have not set CPU or I/O limits on the container. If both values look fine, you are probably not ...


1

This is not a horrible load for modern server hardware. On a write heavy DB its going to be dependent on the Hard-disk layer, triggers, functions, indexes and constraints that have to be processed/checked. An easy way to get to the information you want is to install pgAdmin, and use the dashboard to see what is going on. Number of connections, ...


2

You can see that your second query has Rows Removed by Filter: 18562, while at an average, the first query has Rows Removed by Filter: 1875532. If you run 50 individual queries, the optimizer will optimize each of them individually, and in the cases where only few rows satisfy the filter condition, it will probably choose a different and better execution ...


2

There are two indices on (insert_time) and (insert_time DESC). B-tree indices can be scanned backwards at practically the same speed. And insert_time is NOT NULL, so there is no point whatsoever. Drop one of those in any case. I made some assumptions where info is missing: Current Postgres 12. You are free to redesign the table and lock the table ...


Top 50 recent answers are included