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
2answers
29 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
19 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
34 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
35 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
20 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
39 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
61 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
66 views

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

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
27 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
15 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
20 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
12 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
27 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
53 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
43 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
66 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
22 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
78 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
58 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
52 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
86 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
28 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 ...
2
votes
1answer
83 views

Performance difference in accessing differrent columns in a Postgres Table

Created a table with 200 bigint column, 200 varchar column. (Postgres 10.4) create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint,....., int200 bigint, char1 varchar(255),......, ...
2
votes
2answers
152 views

Why is this query with WHERE, ORDER BY and LIMIT so slow?

Given this table posts_lists: Table "public.posts_lists" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+-...
1
vote
1answer
50 views

Postgres bypassing index with IN clause with 100+ values?

I came across this on another forum: PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
1
vote
1answer
62 views

How to improve performance of query for distinct suppliers in big sales table?

I have two tables, one with store data: Table "public.store" Column | Type | Modifiers -----------+------------------------+----------- id | integer ...
0
votes
0answers
55 views

PostgreSQL: Improving performance of data insert

I would like to migrate all data from DB2 into PostgreSQL. How should I configure PostgreSQL for best performance during the initial migration (loading the data with INSERT statements)? Should/could I ...
2
votes
1answer
42 views

Why is my planning time consistently slower than execution time?

On small tables with 5,000 rows I get: SELECT category_id FROM widgets WHERE category_id = 1; Index Only Scan using widgets_category_id on widgets (cost=0.28..5.20 rows=26 width=8) (actual time=0....
3
votes
1answer
109 views

Use array_position() function to get the most frequent value for column from pg_stats

I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer: SELECT array_position(most_common_vals, 1000) FROM pg_stats ...
0
votes
1answer
50 views

PostgreSQL - VACUUM speed (Heroku)

My DB have a main table that is has lots of commits on a daily basis, with about 15 indexes on this table. The table has 25M rows and the dead row count seem to exceed the number of live rows at the ...
0
votes
2answers
60 views

Add column with default in postgresql without table level lock

Have such a problem - table with over 20mln rows. When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time. So instead of ALTER ...
0
votes
1answer
89 views

Store large compressed documents in Postgresql

Where I work we keep approximately 500,000 image files related to processing for our customer in a file system attached to our Redhat 5.11 production server. We are planning an upgrade to Redhat 7.4 ...
1
vote
1answer
30 views

Choosing index fillfactor for large, highly inserted table

We have a table that will hold records on the 108 order. ╔══════════╦═════╦══════╦══════════════════════════╗ ║ id ║ a ║ b ║ ...many other columns... ║ ╠══════════╬═════╬══════╬════════════...
0
votes
1answer
27 views

Slow query when using ORDER BY in Postgres

This query SELECT * FROM ( SELECT event_id FROM events_201806 WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN' UNION ALL SELECT event_id FROM events_201805 WHERE ...
1
vote
1answer
29 views

PostgreSQL terrible performance with query if tables not in Public schema [closed]

So I have a query which aims to join multiple tables on a common field using LEFT JOIN. When I have these tables in the Public schema, the execution of the query with LIMIT 1 takes less than a second. ...
0
votes
0answers
129 views

Optimizing Transactions Per Seconds For Postgresql

I am doing a benchmarking test for the postgresql database I am using. However, it runs at about 10 transactions per second. pgbench -U postgres -c 10 custom_db starting vacuum...end. transaction ...
3
votes
1answer
58 views

How to optimize a query with a subquery based on a join

I have this query, that finds all active contracts for candidate who have had less than 20 total completed contracts, or less than 5 for the contract site. EXPLAIN (ANALYZE,COSTS, VERBOSE, BUFFERS, ...
0
votes
1answer
51 views

How to involve table index in filtering of 3 conditions?

I have table with 30 mln records and I want to get data by some query very fast. I have these fields: id (int), field1 (int), field2 (int), field3 (int). So I want to query data by this condition ...
3
votes
1answer
151 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 ...
2
votes
0answers
46 views

PostgreSQL - Index Not Used on Large Table

I've the following query SELECT * FROM "items" WHERE (("field1" = 'somevalue' OR "field2" = 'somevalue' OR "field3" = 'somevalue')) ORDER BY "createdAt" DESC LIMIT 20 OFFSET 0; Here is the ...
2
votes
2answers
76 views

Optimizing a simple query joining two big tables

I don't understand why the following query is so slow (with Postgresql 9.6): SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour FROM ...
1
vote
2answers
117 views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
8
votes
1answer
108 views

Slow query times for similarity searches with pg_trgm indices

We added two pg_trgm indices to a table, to enable fuzzy searching by either email address or name, as we need to find users by name, or email addresses which have been misspelled during signup (e.g. "...
2
votes
2answers
74 views

Best way to ALTER date column to timestamp on a large table?

I have to ALTER a date field to timestamp on a huge table having 1 billion records. I am using Postgres 9.5. I learnt that when we use alter query a heavy “access exclusive” lock is acquired, which ...
3
votes
3answers
89 views

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

SELECT something FROM table WHERE primary_key = ? vs. SELECT something FROM table WHERE primary_key = ? AND other_key = ? Say that this is a scenario where the inclusion of other_key does NOT ...
12
votes
1answer
269 views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...