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

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

0
votes
0answers
17 views

Correct deletion of files in pgsql_tmp

Sometimes we have to kill running processes at Operating System level (kill -9 PID) to solve issues related to max_connections value reached. In such cases, we targeted long running SELECT queries to ...
-1
votes
0answers
21 views

Performance optimisation with postgresql query [on hold]

Please suggest some further tuning to reduce responses time SELECT ITS.KeyPanjivaCompanyTrdPartner ,SUM(ITS.ShipmentCount) AS TotalShipments INTO TEMP Top30Companies FROM summary AS ITS ...
0
votes
0answers
28 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
0answers
70 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
64 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
32 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
17 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 ...
0
votes
0answers
29 views

Database and table size shows large difference

I was running a materialized view refresh command in my postgres database. While the query was running it hit out of storage space and crashed with the following error. psql:refresh.sql:1: FATAL: ...
4
votes
1answer
69 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 ...
-1
votes
0answers
4k views

PostgreSQL vs MySQL Configuration file Comparison For Performance

How I can make my PostgreSQL 10 fast like my MySQL, My Hardware: Processor 12 Core RAM 42GB (20GB Ram Already used for MySQL in same machine) HDD 1TB Usage: SELECT-ONLY Searching - LIKE% Column &...
2
votes
1answer
44 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
26 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
94 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
23 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
18 views

What are the Log Settings to be Enabled and set on the Postgresql.conf file for better performance and usefulness?

What are the Log Settings to be Enabled and set on the Postgresql.conf file for better performance and usefulness ? This query is specific for Production. I always get conflicting answers when asked ...
0
votes
0answers
25 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
18 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
40 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
81 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
127 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
41 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
36 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
43 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
23 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
40 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
52 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
22 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
58 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
64 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
74 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
32 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 ...
0
votes
1answer
29 views

postgresql 10.5 slow query issue only on MacOS

Consider these 3 tables Members CREATE TABLE public.members ( id integer NOT NULL DEFAULT nextval('members_id_seq'::regclass), client_id integer, login character varying(255) COLLATE ...
2
votes
0answers
62 views

Setup a PostgreSQL HA with synchronous replication in windows

I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
0
votes
1answer
21 views

Is there a performance gain in specifying the table name in joins and using explicit cast?

Is there any performance gain by the interpreter of the query is I specify the table on a join and make the cast explicit? Instead of ... join table_join on field1_from = field1_join and field2_join ...
1
vote
1answer
57 views

Query takes forever with a NOT EXISTS condition

I have two queries, one which has a condition that uc_id and ce_id are in tableUCP , and another which only requires rows which doesn't exist in the tableUCP table. tableA : ~600k rows tableB : ~...
3
votes
2answers
1k 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
0answers
32 views

Postgres: How to optimize this query which uses multiple json_array_elements() calls

I have the following query which is extracting several columns of data out of a JSON object (facebook_results Postgres 10 column of json datatype). Sometimes the arrays in this object contain 10,000+...
0
votes
1answer
32 views

Need help optimizing a slow Postgres query

It feels like we have all appropriate indexes created, but something feels off that it takes so long. It runs a sequential scan on snippets despite there being indexes on both fields it's filter on. ...
0
votes
0answers
33 views

How can I increase the UPDATE performance for jsonb fields? PSQL 9.6

I have a relatively large table, with less than 10 fields, containing close to a million rows currently but it will accumulate more than 10-15 millions in the near future. One of the fields is: jsonb,...
2
votes
1answer
64 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
0
votes
0answers
150 views

PostgreSQL batch insert performance

I have a C# service that read logs from MQ, create batches and insert them into PostgreSQL 10.2 database. Insertion is currently done by stored procedure that receives arrays of messages, users, dates ...
3
votes
1answer
148 views

Is the system column “ctid” legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by ...
0
votes
0answers
24 views

Cross join with filter causes query planner to mis-estimate results

I have three tables: entity, state, and entity_state_value which links an entity, state, and value. I'm trying to find all entities which do not have a value for a specific state. To this end, I have ...
1
vote
1answer
79 views

Query planning for many-many select and sort query

I have a postgres database with a Genre table (40 records), a Book table (~1.3M records) and a many-many table to relate the two (~2M records). I want to select books by a particular Genre and then ...
0
votes
2answers
72 views

Sequential scan with large sub-query filter “never” ends

I'm using postgres 9.2.4, and run the following query: explain analyze select * from bubu where id not in (select bubu_id from kuku limit 33554431); ...
0
votes
0answers
111 views

Idle (not idle in transaction) connections are not released/closed in PostgreSQL AWS RDS after load test

I'm using C3P0 connection pool and PostgreSQL(10.3) in AWS RDS. I did a load test at low TPS (1 TPS) for 2 minutes, after load test finished, the number of connections were not dropped according to ...
0
votes
1answer
177 views

Optimize Bitmap Heap Scan

I am trying to understand why my query takes a really long time even though I have the required columns indexed: SELECT entity_id, id, report_date FROM own_inst_detail WHERE ( ...
2
votes
1answer
69 views

Optimze performance for LIMIT / OFFEST on collected result of multiple queries

I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly: SELECT 1 : ~5 % rows ...