My system is using PostgreSQL as the database. I am encountering an issue when using a query to join two large tables together. I'll briefly describe the tables as follows: Location: - id: uuid - name: string (indexes) - country:…
I'm working on an express.js project, using Sequelize as the orm, and postgres as the engine. I'm working on improving the performance of a slow select query that does many things, and one of them is comparing the acronym of…
Problem The following query takes 42 seconds when most of the data in not cached: EXPLAIN (ANALYZE, BUFFERS) select count(*) from packages where company_id = 178381; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=395914.63..395914.63 rows=1 width=8) (actual time=42411.940..42411.942 rows=1 loops=1) Buffers: shared hit=21775…
I am dealing with a PostgreSQL (v14) query of this kind SELECT ..., EXISTS ( SELECT 1 FROM table2 t2 WHERE t2.fk = t1.id AND LOWER(t2.table_name) = 't1' ) AS t2_record_exists FROM table1 t1; and was hoping to extract the…
We are trying to optimize a query to a partitioned table, the query looks something like this: SELECT col1, col2 FROM partitioned_table WHERE profile_id = '00000000-0000-0000-0000-000000000000' AND product_id = 'product_a' ORDER BY created_at DESC LIMIT 500; Parent/partitioned table definition: CREATE…
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed. id uuid default uuid_generate_v4() not null primary key, fk_id uuid…
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is 'C'…
I am still a novice to SQL, using PostgreSQL 16.2. create table users( user_id serial primary key ); create table task_pool( task_id serial primary key, order_id integer, clicker_id integer, INDEX idx_order_id (order_id) ); create table task_dispersal( id serial primary key,…
This is my message table. Now I am trying to write an query to group records by collection_id (only first instance) if type multi-store else by message.id . id | collection_id | type | affiliation_id | status | scheduled_for_date --------------------------------------+--------------------------------------+----------------+----------------+--------------+--------------------…
Got this question for a while and wondering if there is any faster query. I have a table with multiple entries per ID, and would like to list all columns with different values for the same ID. ID Brand Type…