3 votes
Accepted

Improving FTS phrase search on postgres for common terms

There is nothing much you can do about that. A GIN index indexes the individual constituents, not the phrase. So the "bitmap index scan" will give you all rows that contain "data" ...
Laurenz Albe's user avatar
  • 50.2k
2 votes

MySQL optimize query against table containing 13M records

We have proper indexes in both the tables In my experience I have never seen MySQL using more than one index per table , so you need a multicolumn index in this case. Add the following indexes, try ...
Ergest Basha's user avatar
  • 3,925
2 votes

Query is much slower with indexes

Create test data: CREATE UNLOGGED TABLE person ( id integer, name varchar(100), age integer, city varchar(20) ) ; INSERT INTO person SELECT n, 'Person '||n, random()*100, 'City '||((...
bobflux's user avatar
  • 1,391
2 votes
Accepted

Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause

Yes, the optimizer rearranges the WHERE conditions as it thinks fit, and all your examples will result in the same execution plan.
Laurenz Albe's user avatar
  • 50.2k
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.2k
2 votes
Accepted

Get All Windows between Start and End Dates

SELECT NameOfWidget, StartEffectiveWhen, CASE WHEN EndEffectiveWhen >= LEAD(StartEffectiveWhen) OVER (PARTITION BY NameOfWidget ORDER BY StartEffectiveWhen) THEN DATEADD(...
Akina's user avatar
  • 19.6k
1 vote

MySQL optimize query against table containing 13M records

It's a nasty. You are filtering on two tables, and the main table needs a two-dimensional index (which does not exist). I suggest these as possibly helping: user_orders: INDEX(type, state, ...
Rick James's user avatar
  • 77.6k
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.2k
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
Accepted

Cheaper query plan takes much longer to execute

It switched to a sequential scan instead of index scan where there's more memory It switched from GroupAggregate to HashAggregate. The seq scan is an incidental consequence to this change. ...
jjanes's user avatar
  • 39.4k
1 vote

Cheaper query plan takes much longer to execute

You could try to use extended statistics to improve the estimate: CREATE STATISTICS mystats (ndistinct) ON col1, col2 FROM table1; ANALYZE table1; I am not sure if that is enough to make the fast ...
Laurenz Albe's user avatar
  • 50.2k
1 vote
Accepted

How to update a Postgres row atomically, with a computed value that depends on the new values

The best practice is not to store redundant data in a database. My solution would be CREATE TABLE real_avg ( user_id bigint PRIMARY KEY, numer double precision, denom double precision ); ...
Laurenz Albe's user avatar
  • 50.2k
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,391
1 vote
Accepted

Scalability of Postgres for table with large number of indexed columns

Since it sounds like your main curiosity is around tradeoffs of consistency for improved concurrency, the topic you're probably looking to learn about is called Transaction Isolation Levels. This is ...
J.D.'s user avatar
  • 37.1k
1 vote

Scalability of Postgres for table with large number of indexed columns

the app details truly don't matter to the question If you're asking a question, it means you don't know the answer. In this case it is a bit presumptuous to hide information because you feel it's not ...
bobflux's user avatar
  • 1,391
1 vote

Scalability of Postgres for table with large number of indexed columns

Being single-threaded doesn't matter. It is multi-process with shared memory, and the ways that processes manage concurrency is not meaningfully different than how threads do. There are two kinds of ...
jjanes's user avatar
  • 39.4k
1 vote

Scalability of Postgres for table with large number of indexed columns

An individual PostgreSQL database session used to be single-threaded, in that there is a single backend process that processes the SQL statements for the connection. PostgreSQL 9.6 introduced parallel ...
Laurenz Albe's user avatar
  • 50.2k

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