I have following Postgres query that runs over a large table and whenever it does, I observe a high CPU load that reaches 100% : SELECT id, user_id, type FROM table ss WHERE end_timestamp > CURRENT_TIMESTAMP AND (notification_channel1 = true…
I have a script.sh which executes 1 script.sql. It will look for the data in a data table. I have a loop and in this one I have other loops which will look for information in the data table and…
I have big Postgres database(around 75 GB) and queries are very slow. Is there any way to make them faster? About database: List of relations Schema | Name | Type | Owner | Persistence | Access method | Size |…
I have a table named assets. Here is the ddl: create table assets ( id bigint primary key, name varchar(255) not null, value double precision not null, business_time timestamp with time zone, insert_time timestamp with time zone default now() not…
The following codes gives the total duration that a light has been switched on. CREATE TABLE switch_times ( id SERIAL PRIMARY KEY, is1 BOOLEAN, id_dec INTEGER, label TEXT, ts TIMESTAMP WITH TIME ZONE default current_timestamp ); CREATE VIEW makecount AS…
I have a database with over 30,000,000 entries. When performing queries (including an ORDER BY clause) on a text field, the = operator results in relatively fast results. However we have noticed that when using the LIKE operator, the query…
I am trying to debug a query that runs faster the more records it returns but performance severely degrades (>10x slower) with smaller returns (i.e. <10 rows) using small LIMIT (ie 10). Example: Fast query with 5 results out of…
Let's say I have the following PostgreSQL database schema: Group id: int Task: id: int created_at: datetime group: FK Group I have the following Materialized View to calculate the number of tasks and the most recent Task.created_at value per group:…
I'm trying to create the most optimal query where the database would return the names of readers who often borrow sci-fi books. That's what I'm trying to optimize: SELECT reader.name, COUNT (CASE WHEN book.status_id = 1 AND book.category_id = 2…
There are 2 tables, main_table and other_table. A user is allowed access to main_table items if his user_id matches the main_table.user_id column or if there is an entry in other_table where his user_id is in the read_acl column (array using…