Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
0
votes
0answers
7 views

AWS RDS Postgres - Same query run on same-ish data on two "identical" servers runs 100x slower

Slightly noob-ish question/problem: I have a query that behaves differently in two "identical" setups - when run against second server, it is 100x slower. Setup is AWS RDS Postgres 12 - two ...
0
votes
1answer
20 views

PostgreSQL IO Cost logic

I've been trying to get an IO cost number, similiar to how MS SQL provides it, but in PostgreSQL. I have read that using EXPLAIN ANALYZE SELECT ..., there will be a cost in the output, but the cost ...
0
votes
0answers
25 views

High memory crashes on Postgresql

We are running a Ruby on Rails application. Connections to database is the following. RoR => HAProxy( read/write load balancing) => Pgbouncer => Patroni controlled Postgresql 12.6 (3 VMs with ...
0
votes
1answer
48 views

Counting number of records newer than X date matching a condition quickly

I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions. Here is basically what I'm working with: CREATE TABLE ...
0
votes
0answers
14 views

Postgresql POD Master/Slave don't clean the cache when Client stopped sending data

I'm running a Performance Stress test on our backend (NODE JS) using a client(JAVASCRIPT) that generates the stress , with a Postgres DB. I've stopped the client from sending data after 5-6 hours , ...
0
votes
1answer
22 views

Postgres advantages of BOOLEAN NOT NULL vs BOOL with TRUE/NULL

I want to add a flag to a frequently accessed table in my database. It is logically a boolean that is set for a very small portion of the records <0.1%. I want to ensure that I am not causing a ...
1
vote
1answer
77 views

Query a table of domain names to get matching top level domains using index

I need to have a table that will contain a single column with a domain+suffix that will looks something like this: domain_and_suffix google.com ynet.co.il 360.cn I want to be able to query this ...
0
votes
1answer
16 views

What is the correct setting for max_client_conn using session pool mode in pgBouncer?

Without diving so much into web applications or database performance tuning etc, I wish to consult on one thing. pgBouncer has the following settings: ;; Total number of clients that can connect ...
1
vote
1answer
93 views

Why is my spatial query slower in Postgres 13 than in Postgres 11?

Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
0
votes
2answers
48 views

How to optimise this IN query?

Lets say I have this schema Resource(resource_mapping_id:Uuid, reaource_id: uuid, node_id: varchar, date: date, available: boolean, resource_start: varchar, resource_end: varchar) So i have the ...
0
votes
1answer
27 views

PostgreSQL slow performance using "or" operator

Development team on my company built the following query: select distinct c.customer_id from table1 c join table2 l on c.customer_id = l.customer_id join table3 cal on c....
1
vote
1answer
43 views

PostgreSQL Query Optimization - Joining with a lookup table does not use index

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
4
votes
1answer
65 views

INSERT ON CONFLICT DO NOTHING efficiency (PostgreSQL)

In Postgres, is it inefficient to perform an “INSERT… ON CONFLICT DO NOTHING” if >99.9% of the time the record exists and nothing is done? E.g. let’s say I have a Node process which fetches 100k ...
2
votes
2answers
96 views

Why is this SQL query with UNION significantly faster than the same query without?

I'm trying to optimize a query, which never completes on Postgres 12.7. It takes hours, even days, make the CPU goes 100%, and never returns: SELECT "id", "counter", "item_id&...
0
votes
2answers
59 views

Vacuum on large table sometimes takes very long

In a PostgreSQL 11 database server I have a large table (932 million rows, 2150GB, not partitioned) into which I insert about 2-3 million rows every night with a batch job. The table was created with ...
0
votes
1answer
28 views

Postgres changes query plan for prepared statement to something poor after multiple invocations

Postgres 13.4 I have a prepared statement that is similar to this: PREPARE my_statement AS SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND ...
0
votes
1answer
49 views

Postgres Planner not using GIN index Occassionally

Platform: Heroku; PostgreSQL Version 13 I have a table around 46GB and there are two GIN indexes on name and email columns I have a search query that is suppose to search in name and email columns ...
4
votes
1answer
59 views

Optimize query matching first N items of an array

I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
3
votes
1answer
45 views

How to get index scan for OR'ed time-range predicates?

I have table events with fields: id user_id time_start time_end ... And have b-tree index on (time_start, time_end). SELECT user_id FROM events WHERE ((time_start <= '2021-08-24T15:30:00+00:00' ...
0
votes
2answers
79 views

Query Times Worsen on 1 Server Vacuum/Analyze on Postgres 9.6

We have a query which is called approx 15,000 times during the day. The query itself should take a few milliseconds to run. I like to think we have the appropriate indexes in place for the query: ...
2
votes
1answer
28 views

Exclude tables from shared_buffers in postgres

Is there a way to exclude tables from being added to the shared_buffers? I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. There ...
3
votes
0answers
35 views

Nested cursor under update command [closed]

I am building a procedure to label database rows if certain records meet some conditions. I am looping over rows using cursor and for every row I check for some conditions, if conditions are met I ...
1
vote
0answers
22 views

pg_jit_available showing 'false' and jit not working, but config file seems set up properly

I am trying to turn on JIT for testing purposes on a Postgresql 12 server, it was off before. My postgresql.conf has the following properties activated (basically all default values): jit = on ...
0
votes
1answer
31 views

Simple JOIN switches direction based on queried ID only

My query: EXPLAIN (ANALYZE, BUFFERS) SELECT work_token_assignment.work_id FROM work_token_assignment JOIN token ON token.id = work_token_assignment.token_id WHERE work_token_assignment....
1
vote
2answers
50 views

70% of Postgres disk size is not data or indices

I have a few tables in Postgres (12.7) database hosted with an Heroku hobby-basic plan (max size = 10GB). Doing heroku pg:info reports 11GB of used disk, so I'd like to do something about it to avoid ...
1
vote
1answer
39 views

Speed up Postgres OLTP - eliminate plan time

We have an application with millions of similar queries that are executed in the following way: UPDATE flow.thread tr SET state = $1, updated_at = now() WHERE ...
0
votes
1answer
26 views

PostgreSQL - Share data on Network drive [closed]

I currently use a simple Microsoft Access database, stored on the company Network Drive, for my department's data needs. This works quite well as everybody in my team has access to the shared folder ...
0
votes
0answers
55 views

postgreSQL slow query with high shared read

i have a problem with my postgreSQL database, actually i'm developer not DBA so i don't have enough experience to deal with problems like this. I ran this query at my psql: explain (analyze true, ...
1
vote
0answers
18 views

Efficient partitioning of data which is Many-Many (M:N) in PostgreSQL

This is a follow up question to Is Postgres ideal for Many to Many data (M:N) which keeps increasing? To give an overview, Events and Participants are the main entities. There are 1000 events and 10M ...
1
vote
1answer
33 views

basic subquery evaluation

I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
1
vote
1answer
30 views

Why lexeme index not used?

product_lexeme_idx is a gin index on lexeme column. product_pkey is a primary key on id integer column. Query that doesn't use lexeme idx: select "product"."id" from "product&...
0
votes
0answers
23 views

why does a hardcoded value use an index but subquery that returns same id cause full scan on every partition

I have a relatively simple problem- i have a table containing a few billion records which is partitioned by record_type_id if i do select max(data_timestamp) from public.records where record_type_id = ...
0
votes
1answer
50 views

Why index not cached?

After running the same query twice I expected data to be cached, but I see data is still read from disk: -> Index Scan using product_pkey on product (cost=0.42..3.51 rows=1 width=4) (actual time=...
3
votes
1answer
94 views

Why does COALESCE prevent the use of an index on a varchar, but not on a text column?

Imagine a view which contains a COALESCE of two varchar columns from two different tables. The underlying varchars are indexed in both tables. In Postgres 11.6, filtering this view on the result of ...
0
votes
1answer
70 views

Optimizing big sorted join (nested loop) based on value frequency

I have a query involving two tables, let's call them timeline and events, defined as follows: create table timeline ( event_id int, ts timestamptz ); create table events ( id int primary key,...
0
votes
1answer
50 views

What's causing very slow single record update in Postgresql?

I'm stumped by a simple query that takes way too long to update a single row by primary key. It only happens to some rows and not all the time. It's sporadic - for instance, it could affect id=182 for ...
0
votes
0answers
184 views

Postgresql - PID in cancel request did not match any process

When I tried to kill one idle connection. Using the below query select pg_terminate_backend(24861); I am getting the below log. 2021-07-26 18:45:32 UTC:192.168.26.250(56021):postgres@demo_uat:[24861]:...
0
votes
2answers
117 views

PostgreSQL query performance issue

We are sometimes getting poor performance (~14s) when using the following query (PostgreSQL 9.6) to fetch rows from the table items whose ID is present in the table items_categories: SELECT items.* ...
0
votes
1answer
44 views

Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english. I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on ...
0
votes
2answers
61 views

Seemingly simple query with unexpected execution time

I have the following schema : And want to get the last price (based on transaction_datetime) per test_acts.code using the following query : SELECT code, price FROM test_transaction_acts, ...
1
vote
0answers
32 views

How to prioritize indices in Postgres cache?

The server config: RAM = 16 GB #CPUs = 6 The Postgres config: There is only 1 Postgres instance. I.e. no partioning, no clustering, ... shared_buffers = 5GB work_mem = 10MB maintenance_work_mem = ...
0
votes
0answers
27 views

PostgreSQL: Filter for many values is slow

I am surprised by the slow response time of a type of query and am hoping to get some advice. Let's imagine a table person with the columns id (integer and primary key) and age (integer). I would like ...
1
vote
1answer
23 views

Postgres query using index does never finish

So I have the following 3 tables: create table imdb_dev.dim_title ( id uuid not null, tconst varchar(10) not null, title_type_id uuid not ...
0
votes
0answers
34 views

Postgres query Optimzation

Below is the query and explain plan. I can see the bottle neck on the below query which is part of main query. (SELECT MAX(scd_tran_version.file_id) AS fileIdMax FROM scd_tran_version ...
0
votes
0answers
58 views

Postgresql - Optimize query/database (Odoo/Python)

I'm trying to optimize the query below in order to go under 1 second (but i hope to do better than just that). Initially it took more than 4 seconds to execute. The query is generated by the Odoo's ...
1
vote
0answers
50 views

Postgres failing to consistently use best partial indexes when multiple are present

In Postgres 10.17, I have two queries with different WHERE clauses and two partial indexes (idx_s_id and idx_w_id) optimized for each one respectively that should allow index only scans for both ...
0
votes
1answer
67 views

Why is this IN-clause with subquery materialization slow?

Can someone help me explain why the performance of the two queries are so vastly different? (Setup-Code is at the end, DB-Fiddle is here: https://www.db-fiddle.com/f/eEuPWqR6gZcjbeSeWk4tu2/0) 1. ...
1
vote
0answers
39 views

How to interpret pg_stat_user_indexes and pg_statio_user_indexes results?

I have this query to analyze index usage in Postgres: SELECT idx.* , idxio.idx_blks_read , idxio.idx_blks_hit FROM pg_stat_user_indexes idx JOIN pg_statio_user_indexes idxio USING (...
0
votes
0answers
31 views

PostgreSQL - intermittent slowness (very simple queries)

I'm experiencing intermittent slowness when (simple) queries are executed (be it SELECT or UPDATE, I haven't checked inserts/deletes). For example, to execute the query below, usually, it takes less ...
0
votes
1answer
32 views

Help needed to raise performance Postgres SELECT

I have a table in Postgres 9.6: CREATE TABLE public.lml_data ( timestamp_from timestamp without time zone, timestamp_to timestamp without time zone NOT NULL, statcode character varying(16) ...

1
2 3 4 5
15