7

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....


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

You need index support to be fast. Tricky without re-designing your table. The following solution should perform excellently (microseconds instead of seconds), but requires some skill. Buckle up. Expression index on IMMUTABLE function Just take a few leading array elements, say 8. That should be very selective already. More would just make the index bigger, ...


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 ...


5

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 ...


5

Expression index A GiST or (even better) SP-GiST expression index on an inclusive timestamp range should work wonders. CREATE INDEX events_right_idx ON events USING spgist (tsrange(time_start, time_end, '[]')); Rewrite your query with the "range contains" operator @> and match the indexed expression (exactly equivalent to your original): SELECT ...


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

I dislike the name "create_date" for a column that's not actually a date but a timestamptz. Using "created_at" instead. Since created_at can be NULL, this 3rd variant will be faster (even if not by much): CREATE INDEX index_c ON my_table (created_at DESC NULLS LAST); NULL values sort after the greatest value by default. DESCENDING sort ...


4

It's typically a good idea to split up that ugly OR in to a UNION query. See: Why is an OR statement slower than UNION? The first SELECT of the UNION query should melt down to milliseconds with this partial multicolumn index: CREATE INDEX ON changes (user_id, counter) WHERE type IN (1, 3); And after adding ORDER BY counter LIMIT 100. Since the outer ...


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

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

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

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

Every UPDATE in PostgreSQL creates a new version of the row. There is no in-place update. So not only would a new JSON be created, but also all other columns in the table would be copied. Updating part of a JSON is not common in relational databases, or at least it shouldn't be. If you feel the need to do so, you have chosen the wrong data model, and you ...


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 ...


3

There can be many reasons for the switched query plan. A very bad plan typically indicates inaccurate column statistics and / or cost constants. It starts with the bad estimate for the index scan on product_tracking_feed_gid_idx: (cost=0.00..2183.11 rows=71806 width=0) (actual time=124.666..124.666 rows=1799676 loops=2) Produces many more rows than Postgres ...


3

Let's take an analogy with application development. There, a functional spec describes the behaviour of the system. It details, amongst other things, what the outputs should be, how they're formatted and how they're sorted. The programmer is free to implement these requirements in any way so long as the specified conditions are met. A functional spec has ...


3

There is no stock way to micromanage shared buffers that way. And in my experience is not a very fruitful area of effort. I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. Are you looking at shared_buffers itself (with pg_buffercache), or are you looking at the on-disk sizes of the ...


3

The tolerable one with the OR got lucky, because it found 100 matching rows with types of 1 or 3, before it found any of type 2 which had to be checked against the other table. The intolerable one apparently did have to do the check against the other table, and it does it in a very slow way, by looping over all the rows in it. Now it should use a hashed ...


2

the execution plan on Postgres 12 shows it went parallel and apparently It made it worse , where in Postgres 9 it went single threaded. seems like postgres overestimated in every single step and eventually thought that is too much work and consequently it went parallel and made a huge scene for nothing. probably If you update your stats and probably rebuild ...


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