Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

328 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9 votes
2 answers
2k views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
user3000172's user avatar
6 votes
0 answers
3k views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
amtest's user avatar
  • 161
5 votes
0 answers
3k views

Postgres index on timestamp column not utilized

I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column. I have a query as below SELECT * FROM employee WHERE timestamp >=...
Dushyant Sabharwal's user avatar
5 votes
0 answers
10k views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
elBastarde's user avatar
5 votes
0 answers
4k views

PostgreSQL High Disk I/O

I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries. For a performance comparison, I built the datbase both on Oracle and ...
tobspr's user avatar
  • 151
5 votes
2 answers
586 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
ALZ's user avatar
  • 161
4 votes
0 answers
67 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 303
4 votes
0 answers
687 views

PostgreSQL predicate not pushed down (through join conditions)

Consider the following data model in a PostgreSQL v13 system; Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
Michiel T's user avatar
  • 141
4 votes
0 answers
1k views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
collimarco's user avatar
4 votes
0 answers
725 views

PostgreSQL 12 performance

I'm using PostgreSQL on Windows for Planet OSM database and have noticed considerable decrease in performance when upgrading from v10 to 11 or 12. Here are the details of the experiment I conducted ...
yaugenka's user avatar
  • 445
4 votes
1 answer
5k 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,...
Nimit's user avatar
  • 41
4 votes
0 answers
721 views

Postgresql update performance?

We have a table which stores about 20 million records: Table "public.tbl" Column | Type | Modifiers -------------------------+------------...
gerasalus's user avatar
  • 141
4 votes
0 answers
193 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
GT.'s user avatar
  • 215
4 votes
1 answer
117 views

How can I avoid this poor Postgres query plan for a sparse aggregate query?

I have the following database schema: CREATE TABLE foo( id SERIAL PRIMARY KEY, tag INTEGER NOT NULL, val INTEGER NOT NULL ); CREATE INDEX foo_tag ON foo(tag); CREATE INDEX foo_val ON foo(val); ...
Alexis King's user avatar
4 votes
5 answers
5k views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
collimarco's user avatar
3 votes
0 answers
1k views

Postgres - limit window function evaluation to once per partition

I have a table of daily stock quotes with ~40M rows and I'm trying to run a query that spits out the most recent date and the number of stocks that made a new one year high on that date. For example: ...
MattL922's user avatar
  • 131
3 votes
0 answers
339 views

Ordering of columns in a GIST index

I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). ...
ItIsJustMe's user avatar
3 votes
0 answers
696 views

How to monitor query load, counts, and durations in PostgreSQL

I'd like to understand load (average active), count, and, average time of various queries. This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as ...
Paul Draper's user avatar
3 votes
0 answers
801 views

Postgres configuration for high-latency storage

I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
Mohammadreza MontazeriShatoori's user avatar
3 votes
0 answers
9k views

Steps to unlock a locked select in postgres

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins. Since it was taking too long, I cancelled it. Now I'm trying to run a query to count records from the inner join but ...
nariver1's user avatar
  • 131
3 votes
0 answers
89 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 ...
offbynull's user avatar
  • 131
3 votes
1 answer
1k views

How can I make full text search on larger text rows more efficient?

The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they often run in a timeout or throw an error: NOTICE: word is too long to be ...
Spedo De La Rossa's user avatar
3 votes
0 answers
2k 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 ...
Ragav's user avatar
  • 131
3 votes
0 answers
4k views

Npgsql Timeout for simple query when run in parallel

I'm struggling to determine the cause of an exception when the code is run in parallel, but works fine when run one at a time. As background information, I'm running a set of unit tests, where each ...
berkeleybross's user avatar
3 votes
0 answers
3k views

Slow query caused by nested loop on simple join?

I've been struggling with the performance on a query. Trying to do a lookup of a large set of ids in a large table (500GB). I create a temporary table of the ids with the ids as a primary key and run ...
postelrich's user avatar
3 votes
1 answer
414 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 ...
remi bourgarel's user avatar
3 votes
0 answers
523 views

Adding partial unique index very slow

I attempted to create a partial unique index for a table like example 11-3 here: CREATE TABLE tests ( subject text, target text, success boolean, ... ); CREATE UNIQUE INDEX ...
Manjabes's user avatar
  • 181
3 votes
2 answers
3k 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 ...
Victoria Stuart's user avatar
2 votes
0 answers
85 views

Query not using foreign key indexes

I have the following schema for my database: CREATE TABLE companies ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE employments ( id SERIAL PRIMARY KEY, name VARCHAR(255), ...
Gabriel Michelassi's user avatar
2 votes
0 answers
333 views

Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?

I'm currently using Postgres 12. A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
K.Banks's user avatar
  • 21
2 votes
0 answers
302 views

Does autovacuum to prevent wraparound on a TOAST table lock its associated user table?

My understanding of the difference between autovacuum versus autovacuum to prevent wraparound is that the former can be interrupted by incoming transactions, whereas the latter will temporarily block ...
Leon's user avatar
  • 21
2 votes
1 answer
134 views

PostgreSQL imbalanced index query optimization

I am having performance issues with my query because the index is heavily imbalanced. I have a column family_id on a table. The table has about 150 million records. This family_id column identifies ...
thijsvdp's user avatar
  • 121
2 votes
1 answer
181 views

Skip (or speedup) postgres unique index creation if I know beforehand that it's unique

I have a large Postgres table (dbeaver says it has about 3e9 rows and almost 4Tb of disk space) I need an UPSERT, but ON CONFLICT section fields don't contain PK and I can't know it without performing ...
Alexander Dmitriev's user avatar
2 votes
0 answers
225 views

Indexing 250M URLs for infix search in PostgreSQL

I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
Jason's user avatar
  • 183
2 votes
0 answers
106 views

How the check table access patterns on postgresql

I have a fairly large table (250 M rows growing with 50-60 k rows a day) and am looking for the best way to check how the table is accessed. The idea is to identify the slowest queries on that table ...
Bart Jonk's user avatar
  • 305
2 votes
1 answer
257 views

How to gauge performance of switching to PostgreSQL table partitioning?

I have a table in a PostgreSQL database containing about 100m records that's mostly read-only with a few monthly writes to update new data. It has over 300 columns, and is heavily indexed for specific ...
Cerin's user avatar
  • 1,345
2 votes
0 answers
7k views

UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise

I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows). But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
étale-cohomology's user avatar
2 votes
0 answers
1k views

How to reduce the cost of a nested loop in a PostgreSQL query

PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012. My table uniteobservation has 824 605 rows: CREATE TABLE activite.uniteobservation ( gid serial NOT NULL, numunobs character varying(20), typunobs ...
Leehan's user avatar
  • 207
2 votes
0 answers
393 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 ...
bakamitai666's user avatar
2 votes
0 answers
412 views

Drop intermediate results of recursive query in Postgres

I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table. Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
Jason Dreyzehner's user avatar
2 votes
1 answer
2k 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 ...
Lucia Sugianto's user avatar
2 votes
0 answers
446 views

PostgreSQL - INSERT if it doesn't exist/RETURN if it does (query optimization)

This query will insert a new IP if there isn't one and return it's ID. And if there already is the same value it only returns the ID. I'm a newb with SQL and am curious if this could be further ...
Nikk's user avatar
  • 121
2 votes
0 answers
426 views

Postgres NOT IN performance

I'm currently doing some exploratory work with Postgres 12, and have bumped into an issue I want to try and understand more about. My background is primarily Microsoft SQL Server, so I'm looking at ...
Callie J's user avatar
  • 492
2 votes
0 answers
147 views

Table recent performance degradation

So on my current project we have an ETL process that on its final stage ends up writing about 35 million of records on table on a PostgreSQL database (PostGIS actually). The version we are currently ...
javier-sanz's user avatar
2 votes
1 answer
399 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 ...
Alexandre's user avatar
2 votes
0 answers
229 views

What's the more performant database design for query speed and write overhead when storing many boolean columns?

Setting the scene I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
Niels Kristian's user avatar
2 votes
0 answers
3k 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 ...
Lachy's user avatar
  • 21
2 votes
0 answers
1k 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 ...
ConanTheGerbil's user avatar
2 votes
0 answers
509 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 ...
CherryBelle's user avatar
2 votes
0 answers
52 views

How can I efficiently search for the most recent rows where a column matches any result from a different query?

I have the following schema: CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE ); CREATE TABLE friends ( user_id BIGINT NOT NULL REFERENCES users, ...
mkslaf's user avatar
  • 21

1
2 3 4 5
7