New answers tagged

0 votes

Data servers: Do query optimisers re-write queries removing redundant columns during the plan creation?

The answer is yes. Typically, an optimiser will drop unnecessary columns, and will also drop joins if possible. The most obvious case of dropping columns is where * is used to select all columns at an ...
Steve's user avatar
  • 319
1 vote
Accepted

MySql Aurora - how often to run ANALYZE TABLE?

It's rare for ANALYZE TABLE to make any significant improvement on query optimization. You can run this command pretty freely. It's pretty low-impact. All it does is read a few pages of InnoDB ...
Bill Karwin's user avatar
  • 14.3k
-1 votes

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

Your 3 questions, answers Yes Yes max_execution_time Yes View my profile.
Wilson Hauck's user avatar
  • 1,728
-1 votes

Why is it so fast to create an index on a table?

Usually create a index on one column is much faster than you thought it should be. For example when you create an index on User.name, usually the column will not exceed 31bytes and row will not exceed ...
ramwin's user avatar
  • 99
0 votes

How to make this postgresql query to use composite index only ? Any Suggestion

The subqueries in the SELECT list are what is expensive; they are executed for each result row. Use the power of filtered aggregates: SELECT fi.runid, count(*) FILTER (WHERE action = 'Add' and ...
Laurenz Albe's user avatar
  • 50.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.5k
0 votes

Strategy for Postgres updates with very high write load, but low consistency requirements

let's say we have 100 users querying the database per second on average, and each one fetches a result set consisting of 200 thumbnails. Then the total writes per second needs to scale to 100 * 200 / ...
bobflux's user avatar
  • 1,436
0 votes

Performance impact when querying without partition key on a partitioned table

(To many questions to fit in a Comment.) I assume that id is not AUTO_INCREMENT? Please provide the query; many details could make a difference. In particular, I cannot answer your "why" ...
Rick James's user avatar
  • 77.7k
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.5k
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.6k
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.2k
0 votes
Accepted

Terrible performance joining a view to a fast subquery / CTE

We ended up converting these views into a series of materialized CTEs, and inverting some of the dependencies between them to reduce duplication. Originally the base DB views were intended to be ...
Brian Moeskau's user avatar
0 votes

Why filtered index on IS NULL value is not used?

I had the same situation. To me solution was to include the needed column (the one from the index filter) into the INCLUDE fields. I find it cheaper than putting it into directly searchable part of ...
userfuser's user avatar
  • 149
0 votes

Performance difference between multiple tables and indexed single table

Single table, PRIMARY KEY(symbol, timestamp). That serves as a unique index, so there is no need to have a redundant INDEX(symbol, timestamp). Please include the main queries you intend to apply to ...
Rick James's user avatar
  • 77.7k
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.5k
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.5k
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

Top 50 recent answers are included