2 votes

How can functions that make heavy use of table variables be optimised?

Cheat: Remove the table variables, convert the function to an inline table-valued function, and hope for the best. Cheat: Convert the table variables to temp tables, convert the function to a stored ...
J.D.'s user avatar
  • 37.1k
2 votes

How to optimally do Postgres `IN (...set...)` query with a large set of values

The limits appendix will inform you that the maximum number of query parameters is 65535, and if memory serves, the limit for a message (query) is half a GB. Naturally, performance will gradually ...
Laurenz Albe's user avatar
  • 50.4k
1 vote

Effect on execution plans when a table variable has a primary key

revival I forget what I was doing in 2017 when this was originally asked, but I figured I'd throw a couple examples in of when indexing a table variable may provide some benefit. There are many ...
Erik Darling's user avatar
  • 40.5k
1 vote
Accepted

How to improve performance for this query ? Any Suggestions

Creating a test setup: CREATE UNLOGGED TABLE bigtable ( runid INTEGER NOT NULL, type TEXT NOT NULL, action TEXT NULL, status TEXT NULL ); INSERT INTO bigtable ...
bobflux's user avatar
  • 1,436
1 vote

Optimizing Reads on versioned table with multiple ANDed "IN" clauses

Test data: CREATE UNLOGGED TABLE resource_operation ( id bigint not null primary key, isdeleted boolean not null, rid INTEGER NOT NULL, --rids are just ...
bobflux's user avatar
  • 1,436
1 vote

Optimizing Reads on versioned table with multiple ANDed "IN" clauses

The query itself should be simple: SELECT * FROM (SELECT DISTINCT ON (rid) * FROM resource_operation WHERE actorrid IN ('ID-1', 'ID-2') AND resourcerid IN ('ID-3', 'ID-4', 'ID-5') ...
Laurenz Albe's user avatar
  • 50.4k
1 vote

PostgreSQL enforcing order dependent constraints within a query

"If, But, Maybe and Unless ..." style thinking is not good for databases to deal with. If a field is supposed to contain JSON, then it should always contain JSON, even if it's an empty ...
Phill  W.'s user avatar
  • 8,578
1 vote

PostgreSQL enforcing order dependent constraints within a query

Unlike in C where operator "&&" implies short circuit evaluation in a specific order, there is no such thing in SQL and the optimizer is free to evaluate your conditions in any order....
bobflux's user avatar
  • 1,436
1 vote
Accepted

PostgreSQL enforcing order dependent constraints within a query

You can put in an optimizer barrier: WITH q AS MATERIALIZED ( SELECT ... WHERE table_3.value is json parseable ) SELECT ... FROM q WHERE q.value->>'foo' = bar
Laurenz Albe's user avatar
  • 50.4k
1 vote

Optimized conception for score table

Just storing the total isn't really comparable, as it gives you little info on what actually happened. Most likely you would be comparing to maintaining both the Answer table and the User table. A ...
Charlieface's user avatar
  • 12.5k
1 vote
Accepted

Optimized conception for score table

FWIW, I know you're asking about logical design so this is more theory-based, but knowing which database system you plan to use actually is relevant to the answers, based on features available within ...
J.D.'s user avatar
  • 37.1k
1 vote

Table partitioning for data pruning purposes

A solution that I have used in some of my projects is To flag the expired records. Use a procedure to insert a new record, which uses an expired record if it exists, otherwise it creates a new one. ...
Rohit Gupta's user avatar
  • 1,592
1 vote
Accepted

The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value

Thanks very much for your response. I solve my problem by taking a Dedicated server instead of VPS. The old VPS was : 120Go RAM 24 Core AMD CPU NVMe as storage Ubuntu 22.04 The new is : 64Go RAM 18 ...
Nelson Nokimi's user avatar
1 vote
Accepted

How to optimally do Postgres `IN (...set...)` query with a large set of values

I made a small benchmark. Source code on pastebin Test table: 10M rows with (id INT PRIMARY KEY, s TEXT). Results: 0.055ms 1 rows Correlated SELECT * FROM test_array WHERE id =ANY(A 0.042ms ...
bobflux's user avatar
  • 1,436

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