6

Yes, this will modify all rows, even if the value doesn't change. If you want to avoid that, either use a WHERE condition: UPDATE app.products SET "description" = TRIM(BOTH FROM "description") WHERE "description" IS DISTINCT FROM TRIM(BOTH FROM "description"); or use the suppress_redundant_updates_trigger() trigger ...


6

That's the rule the SQL standard sets (and MySQL ignores some of the rules the standard defines and allows invalid SQL). You can't use a column alias on the same level where you defined it and having is only allowed in a query that uses aggregation. If you want to avoid repeating the expression, use a derived table. It's also typically faster to use NOT ...


6

Explanation The all-important difference between the two query plans is the added read=xyz bit in multiple places of the slow version. Slow: Buffers: shared hit=116296 read=42298 Fast: Buffers: shared hit=158122 This tells you that Postgres encountered data (or index) pages that were not cached, yet. Repeat the slow query (possibly more than one time, ...


6

Interesting to hear that 10MB is "a significant amount of memory". A database is not a web server, which is optimized for serving lots of short-lived connections. A PostgreSQL connection loads cached catalog data for efficiency. That is why you use a connection pool, so that all your short database requests are handled by a small number of ...


6

Function inlining is important, and applies here, too. Your PL/pgSQL function cannot be inlined. (Besides being overkill to even call another function for the trivial expression.) But since it's still very cheap and only called once, it's not the issue here. Whether you use the OFFSET 0 hack or WITH CTE t1 AS MATERIALIZED, either prevents repeated evaluation....


5

PostgreSQL estimates the sequential scan on value_aggregation_hour slightly cheaper than the index scan (233000 vs. 236000), while in reality it is much cheaper. The row count estimate is very good, so the problem is probably that PostgreSQL has a wrong idea about your machine. You could try to improve that: set effective_cache_size to the amount of memory ...


5

No, this shouldn't affect the speed of queries for other tables. One way it could indirectly affect the speed is, if you regularly run queries against that big table and retrieve many rows from there (either directly or indirectly e.g. because of a Seq Scan). This could then cause data from other (smaller) tables to be evicted from the cache. Then a query ...


5

If you are using PostgreSQL v13, you can install pg_stat_statements, which logs the amount of WAL per statement in the wal_bytes column. So you could run CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT wal_bytes, calls, query FROM pg_stat_statements ORDER BY wal_bytes DESC LIMIT 10; The WAL itself has no connection to a certain SQL statement, but ...


4

Your query must be using an index on "id" to scan the index in the implied order, and then filtering out everything where "user_id" does not equal 123, stopping after it finds 31 rows which survive the filter. Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows ...


4

You just need an aggregative query using GROUP BY like so: SELECT service_uuid, ((5 * SUM(five_rating)) + (4 * SUM(four_rating)) + (3 * SUM(three_rating)) + (2 * SUM(two_rating)) + SUM(one_rating)) / (SUM(five_rating) + SUM(four_rating) + SUM(three_rating) + SUM(two_rating) + SUM(one_rating)) FROM RatingsTable GROUP BY service_uuid Also ...


4

Index The query plan spills that you have an index payments_last_updated. That's all we need for payments. As for assessmentreports: There can be multiple entries in assessmentreports for one payment_id each with a different kind. So there could (should) be this UNIQUE index: CREATE UNIQUE INDEX assessmentreports_payment_id_kind_uni ON assessmentreports (...


4

To answer your basic question: No. timestamptz is stored as 64 bit integer quantity internally (same as int8). An index on it performs identically to one on a bigint (int8) column - when used correctly. Related: Ignoring time zones altogether in Rails and PostgreSQL If in doubt, go with timestamptz. It's built for the purpose. The only argument in favor ...


4

You can use a lateral cross join, although I don't understand why you want to avoid a CTE: SELECT dim_date.date AS "date", stage_net_subscription_020_classes.client_id AS client_id, stage_net_subscription_020_classes.product_id AS product_id FROM star.dim_date dim_date CROSS JOIN stage.stage_net_subscription_020_classes CROSS ...


4

The cache stores blocks read from from disk. A single block contains one or more rows from a table. As both queries read the same data, they request the same blocks. So yes, the second query will be reading the blocks from the cache.


4

A side effect of this command is that you take an exclusive lock on the table: ALTER TABLE DISABLE trigger ALL; The manual on the DISABLE TRIGGER clause: This command acquires a SHARE ROW EXCLUSIVE lock. About SHARE ROW EXCLUSIVE: [...] This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it ...


3

No, there is no real row number threshold. If you only have queries that select rows by primary key, the size of the table doesn't really matter. Partitioning is also primarily a management tool to quickly remove no longer needed rows, not so much a performance tool. It can be used to improve performance, but only if you have queries that only need a (small) ...


3

I'll ignore the "most databases" in this question, otherwise I'd have to vote to close for lack of focus. Instead, I'll answer about PostgreSQL. To "merge" the lists in your question, you'd have to build the intersection: WHERE id IN (1, 2, 3, 4) AND id IN (3, 4, 5, 6) is the same as WHERE id IN (3, 4) PostgreSQL doesn't do that ...


3

Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need: DO $$ BEGIN DELETE FROM tbl WHERE … AND counter = 1; -- common case first! IF NOT FOUND THEN UPDATE tbl SET counter = counter - 1 WHERE …; END IF; END $$; Should also be cheaper than a trigger solution, where a trigger ...


3

The main reason for the different query plan is probably the increased number of rows that Postgres estimates to get back from projects: (cost=0.00..42021.35 rows=10507 width=0) (actual time=35.642..35.642 rows=10507 loops=1) vs. (cost=0.43..277961.56 rows=31322 width=4) (actual time=0.591..6970.696 rows=10507 loops=3) Over-estimated by factor 3, which is ...


3

A lateral join might help to reduce the work the group aggregate needs to do, as apparently the planner isn't smart enough to push the roll_id into the derived table: SELECT result.operator_ids FROM userroll roll JOIN LATERAL ( SELECT result.roll_id, jsonb_agg(result.operator_id) AS "operator_ids" FROM userrollresult result WHERE ...


3

This doesn't need an inner select or a lateral join at all. You can do the aggregation directly at the top level: SELECT jsonb_agg(result.operator_id) AS "operator_ids" FROM userroll roll join userrollresult result using (roll_id) WHERE user_id = 10 group by roll_id ORDER BY roll.time DESC LIMIT 20; This may or may not be faster than the lateral ...


3

use UNLOGGED tables throughout set shared_buffers big enough to contain the whole database if you have bigger queries, increase work_mem have enough RAM to contain shared_buffers plus work_mem times the number of client connections Typically, you can gain most by tuning the queries that use most of the time.


3

Ideal for those queries would be in index on (court_id, id), and with the columns in that order. It should be extremely fast in either direction. And once you have it, you should be able to get rid of the plain index on court_id as it wouldn't be much good anymore.


3

EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original. EXPLAIN (estimating the cost) suggests 40,089.36 vs. 189,883.92 unicorn points. But EXPLAIN ANALYZE (measuring actual execution times) disagrees and shows 2,502.031 ms vs. 1,835.193 ms, so around 1/3 slower. There can be many reasons why the ...


3

You looked at the estimated costs of the query and saw that it was less, but you overlooked two important things. The actual measured time was longer (although not by much), not shorter. And the actual row count was off by a factor of 32 from the estimate. Both of these are pretty important flags. So while you apparently did the EXPLAIN with ANALYZE, you ...


2

That query will get faster if you increase work_mem (because then there will be no more "lossy" blocks). The idea to first select all ids from one table and then select rows from another table based on these ids is fundamentally wrong. You should instead join the two tables and do the same work with a single query.


2

This part of Citus is a cohesive solution the purpose of which is sharding. Neither FDW nor partitioning are that. They are each a stand alone features and when they work together they do so at arm's length. In particular, there is no FDW API for bulk inserts, so it is converted into one insert statement per row, which is slow relative to bulk inserts. ...


2

See Building Indexes Concurrently in PostgreSQL documentation. CONCURRENTLY option of CREATE INDEX solves the problem in an easy way. Just add it to CREATE INDEX. Be aware that while CONCURRENTLY option avoids lags, it does slow down building the index and that CREATE INDEX with CONCURRENTLY cannot be used in a transaction.


2

If an average of the current rows size is wanted, you could use pg_column_size : SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename; Using it per column : SELECT SUM(pg_column_size(table_name.column_name))/COUNT(*) FROM tablename;


2

Assumptions agency has fewer rows than the "millions and billions" you mention for other tables. Way below the range of integer: -2147483648 to +2147483647. Else we need bigint for internal_id to begin with. But agencyis still big. Else, don't bother with the index optimizations below. Both internal_id and external_id hardly ever change. ID ...


Only top voted, non community-wiki answers of a minimum length are eligible