Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
0
votes
2answers
35 views

Postgresql query optimisation with left join

I'm trying to optimize query run on Postgresql 12. There are 2 tables users (5 mln rows) grants (8 mln rows) I'm running a following query SELECT * FROM users u LEFT JOIN grants g ON u.id = g....
0
votes
1answer
38 views

Postgres Speeding up Query to to calculate Intersection over Union (IoU)

I am attempting to optimize the following query, and have gotten to a stage where I am pretty stumped on what I could do to improve. Given a spotify database, essentially what I am trying to do is &...
0
votes
1answer
38 views

How to turn off "autocommit" in PostgreSQL

I am working with PostgreSQL version 11, and would like to turn of autocommit. Either permanently or per query through a pl/pgsql command. In the documentation it says it is turned off by default, ...
1
vote
1answer
35 views

Postgres v10 is not using the index in a subquery

I have a subquery that will return 3 records, to later add the result of some calculations (using some functions). When I am running the full query, Postgres is calculating a high number of expected ...
-1
votes
0answers
29 views

Postgres Materialized View / FDW performaance

I am looking at the performance of a reporting application. The architecture is a summary matview (top tier) joining matviews (mid tier) that respectively join matviews (bottom tier) that query ...
1
vote
1answer
32 views

Specific index issue with postgresql planner/optimizer

i have an issue where i have no idea anymore and can't seem to find a similar one already discussed, so... Am/have to be on pgsql 10.15 / RHEL x86_64. rel A has about 813 million records. format is ...
0
votes
1answer
47 views

Improving cold query performance with pg_prewarm

We have a projects UI page where users can search projects. I noticed that search queries will be slow the first run (up to 10 seconds) and faster on subsequent runs (around 2 seconds). Could this ...
1
vote
1answer
21 views

Temp files and work_mem

My Postgres instance is logging things like this: 2021-11-16 22:18:52.491 UTC [1808765]: [3-1] db=xxx,user=xxx LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp1808765.0", size 262144 My ...
0
votes
1answer
56 views

Postgres optimizing IN queries

I am trying to optimize the 'IN' QUERY in Postgres 11. I tested 3 approaches: Case 1: DELETE FROM mytable WHERE id1='fffe9411-3b9d-40dc-9cc6-14407785be8b' and id2 IN ('00000140-1ae9-41f7-9614-...
1
vote
1answer
30 views

Find bloated tables and indexes in PostgreSQL without extensions

I have tables that change a lot during the day, lots of data is deleted, modified and inserted. I suspect that the tables and indexes on those tables might be bloated. I've seen that there are ...
-2
votes
0answers
18 views

Database schema review and optimization for a collectibles marketplace

I'm in the process of designing a database schema for a crypto collectibles marketplace and was hoping to get some suggestions and thoughts and how to improve it and make it more normalized and ...
1
vote
1answer
50 views

Implementing fast and deterministic sort order in PostgreSQL with pg_trgm

We have implemented a similarity search with pg_trgm in a PostgreSQL 13 database using a gist index with searchable_column. The table and index have the following setup: CREATE TABLE things ( id ...
0
votes
1answer
18 views

Postgres query pg_views and pg_matviews taking a long time and finally error out

We have the following scheduled query which runs every 15 mins. select schemaname as schema_name, views.viewname view_name, views.definition from pg_catalog.pg_views ...
-2
votes
1answer
51 views

Should I alter an Index to include a filter in Postgresql 9.6

I have a query which I am looking to improve on with potentially a new index. SELECT COUNT(price_id) FROM price.price WHERE ( ( (userid = '3081217') AND ( ...
0
votes
0answers
38 views

PostgreSQL current running statemen [closed]

Normally to see currently executed SQL statements we use pg_stat_activity. Unfortunately, when we deal with functions or pl/pgsql blocks it's impossible to see the specific statement, but whole the ...
1
vote
1answer
41 views

Why does a \timing always show faster times when run from within a script than on the terminal?

Let's say I have a very simple command like SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t; If I open up a terminal and I run \timing and then the above query, I'll consitantly get ...
1
vote
2answers
58 views

Different execution time for same query on different Postgres databases

I have two identical databases, development and live. I run this query on both of them.... select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device) On the dev box the ...
0
votes
1answer
47 views

Which indexes to use for Fulltext search + group by year and month

Final purpose of the query For a query q, I want execute a query like this one SELECT publication_year_month, count(*) FROM content WHERE content.title_content_vector_column @@ plainto_tsquery(q) ...
0
votes
0answers
30 views

Huge overhead of multiple Index JOINs over a single row

Consider the following type of query, which has many joins: select t0.*, t1.*, etc.. from t0 left join t1 on t0.t1_id = t1.id left join t2 on t0.t2_id = t2.id etc.. left join t11 on t0.t11_id = t11.id ...
1
vote
2answers
66 views

Delete old rows from a big table, keeping one per given time interval

I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz): The usual operation to delete from DB/table is very simple such as I want to delete all ...
1
vote
3answers
714 views

High cost in PostgreSQL query

I have a table that has more than 10.000.000 records, and I'm creating a query that returns about 4436 records. It just so happens that it gives me the impression that the query cost to get to the ...
0
votes
0answers
24 views

Are mutliple client-based queries multithreaded internally in PostgreSQL?

This seems to suggest, that if i connect to the same instance of Postgres running on my machine, multiple connections from clients are handled in parallel. Is that true? Can I start 4 shells running ...
1
vote
1answer
44 views

Postgres 13 Sort Performance

Sort performance on one of our query is very bad that it takes up to 14 seconds to run the query. Here is the Query: SELECT "stock_quant".id FROM "stock_quant" WHERE ((((("...
0
votes
1answer
46 views

PostgreSQL RDS: Slower than expected performance when large table / index is not in cache

I have a fairly large table, 43GB and a 14GB index, that consists of time series cost data. I am querying by date and summing the amount. When this data is not in the cache (either OS or Postgres) the ...
1
vote
0answers
51 views

PostgreSQL: Taking into account correlation between null / not null values of columns during planing

Question: Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow ...
0
votes
0answers
14 views

PostgreSQL parallel query not working on CTAS

Hi have a query that looks like this: create unlogged table analytics.processor_data as select col_a, col_b from tbl_a a join tbl_b b on a.id = b.id; It's all good, however, it ...
1
vote
0answers
19 views

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

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
22 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
1answer
53 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
82 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
79 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
18 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
106 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
51 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
28 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
48 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
95 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
103 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
67 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
40 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
54 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
65 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
47 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
90 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
33 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
24 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
34 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
59 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
2 3 4 5
15