5

Multiple parallel threads for the same query all share the same session_id, or spid. Kendra Little did a great write-up showing that here. Brent Ozar Erik Darling also has a great, if only tangentially related, post about it here If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-...


3

On my 2019 instance, clicking the Display Estimated Execution Plan toolbar button and expanding the execution plan XML for a given query, you should see CardinalityEstimationModelVersion="70" (old) when using the FORCE_LEGACY_CARDINALITY_ESTIMATION hint and CardinalityEstimationModelVersion="150" (new) when NOT using the ...


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


2

I want to know the methodology of testing the writing speed. I am assuming you want to know if queries are running faster. It can be read, write, or a combination of both. There are many approaches you can take. It depends on the usage pattern of the table. I am sure I won't be able to list everything, but the following points will give you enough tools to ...


2

Keywords in square brackets in the synopsis of SQL commands in the manual are optional. And in this case just noise. Add them or leave them, no functional difference. (The verbose form SET DATA TYPE conforms to standard SQL.) bigint occupies 8 bytes, integer occupies 4 bytes. A table rewrite is inevitable. The manual: As an exception, when changing the ...


2

Your view gives the correct answer, while your CTE gives the wrong answer, using the oldest date, not the newest. If you want to use an index scan (although in my hands it doesn't actually make a difference in performance), specify DESC for both ORDER BY columns, or create an index for (currency, date DESC).


2

The query simply has to read the 98175 rows which are located in almost as many 8KB-blocks, so there is no way around reading all these blocks, and if they are not cached, that is going to be slow. Your only chance to make that faster is to make sure that the rows are lumped together in fewer blocks. That could be done by rewriting the table like this: ...


1

Can I do this against a live production table, or do I need to bring down the database? Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1). But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on ...


1

here are a couple of ideas. One does the constraint check in a function. the second modifies the table, creates a trigger to add in the missing data and creates a new index on the three fields that have to be checked CREATE TRIGGER check_jsonb BEFORE INSERT ON data FOR EACH ROW EXECUTE PROCEDURE CREATE FUNCTION public._check_jsonb()...


1

If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea: remove the uniqueness constraint when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates have some sort of parallel process going through the table periodically and removing duplicates


1

I would go with TINYINT for these reasons: You might come up with other modes - for example, "verifying" Bit can't be aggregated - that usually comes up with PIVOT function, where you have to aggregate - you first need to cast to int family type. Single bit column doesn't actually provide storage savings - it still takes a byte (the first bit in a byte) - ...


1

It's highly unlikely that there is a material performance difference here. So the choice is between create table AppUser ( Id int identity primary key, Name nvarchar(200) not null, UserAccepted bit null ) And create table UserStatus ( Id tinyint primary key, Description varchar(200) ) create table AppUser ( Id int identity primary key, ...


1

I'm not a hardware expert, but why do you think a SAN should be comparable to a NVMe? Sorry, there is no simple formula. At what scale did you initialize the benchmark tables? that is, what number for pgbench -i -s 20? How many clients did you run the benchmark? (What numbers did you provide for -jand -c?) Did you run pgbench on the same server as was ...


1

Your complaint here is rather inconsistent. You talk about simple queries that just sort on updated_dt, but that is not the query you show. Also, the query you did show did not take 15-30 minutes. Is there another even simpler query that takes even longer? If so, that would server as a better example. Particularly if you turn on track_io_timing first. ...


1

This is going to vary between databases, but there are things that we can say are generally (but certainly not always) true of common databases such as SQL Server, Postgres, etc. Drive IO generally works in blocks, usually these days of 4Kbyte or 0.5K, and to write any of a block the drive will write the whole block. Database engines organise their ...


1

Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with ...


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