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....
2
votes
1answer
168 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...
2
votes
1answer
307 views

How to deal with seldom used big database and postgresql?

I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries. My problem is : This database is ...
2
votes
1answer
146 views

Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors

I know very little about Postgres memory settings. I have developed a paid-access database, which mostly stores text (and some metadata) 2 tables have 10 millions rows with little text (1 paragraph ...
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, ...
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 &...
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 ...
0
votes
2answers
856 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
2
votes
3answers
334 views

Execution Performance Issue with Similar Query

I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows. If I run a query only on the ...
-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 ...
2
votes
1answer
2k views

Using pg_prewarm to load X number of latest rows into cache

We have a large query that is extra slow when customers "run it the first time, early in the morning..." So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount ...
6
votes
1answer
2k views

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

Is there a difference between a plain SQL function doing a SQL query: create function get_sports() returns setof sport as $body$ select * from sport; $body$ language sql stable; and PLPGSQL ...
1
vote
1answer
94 views

Postgres database insert become slow after 10 days, and then it needs a full vacuum

We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and ...
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 ...
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 ...
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-...
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
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 ...
2
votes
1answer
231 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with the ...
-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 ...
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
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) ...
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
90 views

PostgreSQL - tuning multiple clusters on server

I usually use http://pgtune.leopard.in.ua/ for tuning postgresql parameters depending on hardware resources. But now I want to tune two clusters on same server. How should I tune PostgreSQL ...
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 ...
21
votes
2answers
23k views

Slow index scans in large table

Update 2020-08-04: Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are ...
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
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
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
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 ...
9
votes
2answers
8k views

Importing large SQL dump with millions of INSERT statements

I need to import a big .sql file (8.1GB when unpacked) into PostgreSQL. I tried to use \i /path/to/file.sql but it is way too slow. How does one speed up the import? I need to import this data weekly....
0
votes
1answer
279 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
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 ...
3
votes
5answers
4k views

Get a truly RANDOM row from a PostgreSQL table quickly

I always used to do: SELECT column FROM table ORDER BY random() LIMIT 1; For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
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
139 views

PostgreSQL Update Query Hangs

I have an update query being fired from the Java Hibernate (for a single user), which works fine on a small dataset of rows in a table (100MB), but the same update query goes into a hung state if the ...
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
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 ...
3
votes
2answers
534 views

Postgres 11: Not using the correct indices after upgrade

We upgraded lately our database from PostgreSQL 9.6 to 11 on RDS (after the upgrade we did vacuum full and analyze for the DB). We noticed that a frequent query changed its execution plan, and now it ...
3
votes
2answers
2k views

What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
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
399 views

Issues with enabling pg_stat_statements

I am having issues quering pg_stat_statements and puzzled what else I missed? See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not ...

1
2 3 4 5
15