Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-performance]

PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.

0
votes
0answers
18 views

postgresql: live tuples not matching table row count

We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application. In looking into the problem I've noticed that the row ...
0
votes
0answers
15 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...
2
votes
1answer
56 views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
1
vote
1answer
63 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
2
votes
1answer
38 views

Can I use hypertables in timescaledb merely to get better insert rates?

I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc. I have been reading about timescaledb and how ...
1
vote
1answer
54 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
2
votes
1answer
85 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
0
votes
0answers
15 views

Compare PostgreSQL Execution Plans

PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
3
votes
0answers
53 views

Slow SUM/GROUP BY Postgres

I'd like some help with a slow query on postgres 10. We use the following query as a prepared statement and I've noticed that some times the PREPARE or EXECUTE is very slow, but I'm struggling to find ...
0
votes
1answer
22 views

Should we favor multiple INSERTs or multiple UPDATEs

I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves ...
0
votes
1answer
18 views

Update column value divisible by 100

Hello experts I need to update next column value with divisible by 100
2
votes
0answers
32 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
2
votes
1answer
64 views

How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below: select sum(t.amount * t.exchange) from table t; Finalize Aggregate (cost=658027.39..658027.40 ...
0
votes
0answers
25 views

How to get a good balance between the performance of various queries and the number of indexes

With the following table, I'm worried either one of my queries will perform poorly, or I'll have so many indexes that all queries perform well but writes are too slow. Here's my table: ...
1
vote
1answer
48 views

Poor postgres performance when deleting rows with large BLOBs

I have a PostgreSQL table like CREATE TABLE stuff (id BIGSERIAL, data bytea, info TEXT) the data blob is typically in the 1 .. 10 MB range per row. The info text is just a few characters. The table ...
3
votes
0answers
35 views

PostgreSQL Enum : Search on ENUM type

Defined an ENUM CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD'); Using this as a type in a table CREATE TABLE if not exists transaction( id BIGSERIAL NOT NULL PRIMARY KEY , amount NUMERIC(35,...
2
votes
1answer
50 views

System configuration to address poor PostgreSQL INSERT performance

Problem Symptom High CPU usage by postmaster child process associated with client connections attempting to INSERT low-volume rows (results in rows INSERTed 25x slower than using COPY ... FROM STDIN ...
1
vote
1answer
35 views

Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote.... Before running the ATTACH PARTITION command, it ...
2
votes
1answer
64 views

Performance impact of updating target columns with same values ON CONFLICT

This is a question about the inner-workings of Postgres (v10) and performance. Given a table, github_repos, with a multi-column unique index on org_id and github_id columns, is there any performance ...
1
vote
0answers
25 views

How to VACUUM FULL a single partition without stalling the parent?

I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc I've noticed that if I attempt to VACUUM an old ...
2
votes
3answers
50 views

Postgres - how much space is required to perform a VACUUM

I know that doing a VACUUM FULL on a table will free up disk space and return it to the OS. There's been a couple of occasions where my OS (windows) has warned me that it's running low on disc space. ...
1
vote
1answer
38 views

Update “oldest” row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
-1
votes
0answers
34 views

PostgreSQL slow Select Query response for Sequential Scan

I have a table that has roughly 750,000 records. The table has 7 columns one of which is a text field where we have large text, ranging from a few hundred chars to a few KB of data. A simple select * ...
3
votes
1answer
423 views

Postgres 9.0 vs Postgres 10 & 11 Performance

We want to move our databases from webhosting provider (postgres 9.0) to our local network server (tried both postgres 10 and latest 11) Our machine is windows server, fast XEON machine with 16gb ram, ...
6
votes
1answer
79 views

Postgresql is failing to use my covering index and falling back to a much slower bitmap scan

I'm trying to figure out why a table of mine is using a Bitmap Heap Scan when a index scan is dramatically faster. Table: webarchive=# \d web_pages ...
1
vote
1answer
42 views

Why does ENCODE on BYTEA not seem to have any performance impact?

I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64). Initial thought was to enforce a valid hex string storing it ...
0
votes
0answers
35 views

What happens if two processes attempt to update the same row in the same Postgres table at the same time

To clarify the question, what is supposed to happen if two different processes attempt to UPDATE the same row (uniquely identified by primary key) at the same time? - And does it make any difference ...
4
votes
1answer
82 views

PostgreSQL UDF (User Defined Functions) overhead

Disclaimer The task may seem esoteric, but nevertheless I want to create a POC of some sorts. The goal My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
2
votes
1answer
51 views

Performance of count() vs count() in a window function

I'm having a hard time trying to figure the performance gap between a raw count() query vs a count() OVER() window function. Here is the test setup. Let's create a dummy table: CREATE TABLE main (id ...
1
vote
1answer
27 views

PostgreSQL: Slow JOIN between multiple tables.

Context: I am working on a data warehouse that needs to have responsive look ups as it is used as the source for an operator console. The target is to achieve sub 1 Second lookups (ideal would be ...
6
votes
2answers
104 views

Postgres Transaction OOM on 100k DDL statements

We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...
0
votes
2answers
35 views

postgres; do indexes automatically reindex new data?

If I have a bunch of rows in a Postgres table, then create an index, does the index get automatically updated if new data rows are inserted after it's creation? Alternatively stated, is REINDEX ...
0
votes
0answers
26 views

Postgres - manual over-riding the default locks?

Is it possible to over-ride the locks that postgres chooses to create? Here's the situation, I have a table something like this.... CREATE TABLE foo ( recId SERIAL PRIMARY KEY, recordTime ...
0
votes
0answers
19 views

How to optimize (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
1
vote
1answer
46 views

Full text search on larger text rows

I am wondering what is the best practice to use the right query in my case. The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they ...
0
votes
1answer
26 views

postgres table sizing inconsistency

I've been doing some experimentation/testing with VACUUM ANALYSE and table sizing. I've been using the queries here to get a figure for table size, and I've been using this to get approximate ...
2
votes
2answers
94 views

Slow Bitmap Heap Scan, Order by killing the execution time

A query that is going to slow on table users, the table has 5 million records, and for increasing query performance I often index function outputs of aggregated data that are convenient for each case, ...
2
votes
1answer
133 views

ORDER BY ASC is 300x faster than ORDER BY DESC

The ascending sort is 300x faster than descending sort on a column which has a B-Tree index. I have table of around 20 million rows. I have indexes on inserted_at and x columns. Ascending Sort ...
0
votes
1answer
49 views

postgres performance tuning - what to try next?

I have a simple (but large) postgres database. I have a table defined like this…. CREATE TABLE TABLE_A ( MessID SERIAL UNIQUE, rawData CHAR(250), loadedTime TIMESTAMP)...
1
vote
1answer
47 views

Postgresql bad planning with multicolumn gist and search with empty results

I'm having an issue with a multicolumn index (integer, geom), where geom are PostGIS points, and the integer is a point aggregator with business meaning. I do searches by boxes in the geom column, ...
0
votes
2answers
59 views

postgres - pg_stat_statements directory

Does pg_stat_statements use the same stats_temp_directory directory? Our stats_temp_directory is under /var/run/postgresql which resides in tmps. We have about 500 databases in an instance and set ...
0
votes
2answers
34 views

Optimize Postgresql query

I'm struggling to get the below query to perform well. It's doing a sequential scan on the children table even though there's indexes on the join columns. Any ideas? EXPLAIN ANALYZE SELECT "children"....
0
votes
1answer
26 views

How to reduce the required frequency of ANALYSE on a postgres table?

I have a Postgres database into which about 20 million rows are inserted every day. The records are inserted via a powershell script that calculates the average time taken to insert each record. It ...
3
votes
1answer
42 views

What is the difference between a SQL function doing a SELECT versus a PLPGSQL function doing a 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 ...
2
votes
0answers
74 views

changing tablespace seems to waste space

I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month. I ...
0
votes
2answers
23 views

How do I end function when X

so my function looks like this: create or replace function insert_stuff(a1 text, a2 text, a3 int) returns table (id_i int, id_j int) as $$ begin return query with i1(id1) as select insert_here(a1,a2)...
1
vote
3answers
110 views

How to get rid of a Bitmap Heap Scan if the table already has the proper indices

I am running PostgreSQL 9.6. These are the relevant definitions: CREATE TABLE IF NOT EXISTS instagram.profiles_1000 ( id SERIAL PRIMARY KEY, username ...
2
votes
1answer
68 views

Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory?

Here is my query, it takes 2.7 sec: SELECT t2.id, t1.id FROM sample t1 INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex; The associated EXPLAIN (ANALYSE,BUFFERS) output: ...
-2
votes
2answers
77 views

Optimizing performance of basic select count (*) query on large postgresql 9.6.5 table [closed]

I have a python app called 'Links' where users add social posts called 'publicreplies'. This app sees decent traffic - the table containing publicreplies has grown quite large over the past 12 ...
0
votes
0answers
34 views

query runs very slow when :jsonb ?& operator is used

I have the following SQL query that works fast select phone_number.id, phone_number.phone_number, phone_number.account_id, phone_number.used AS used, ( now() AT TIME ZONE ...