Questions tagged [postgresql-performance]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
24 views

More efficient group by to count items in group

My query is: SELECT COUNT("EventType"."id") AS "eventCount", "EventType"."id" AS "EventType.id" FROM "events" AS "Event" INNER JOIN "event_types" AS "EventType" ON "Event"."eventTypeId" = "...
1
vote
3answers
39 views

Most efficient way to insert data into archiving table without influencing production?

For example lets say we have table A: create table if not exists T( column1 int, column2 varchar, column3 date ); and archiving table TArchive: create table if not exists TArchive( column1 int, ...
0
votes
1answer
11 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 ...
1
vote
1answer
36 views

HLL aggregation query is very slow

I have the following query select ts_date, sum(bids_placed), hll_cardinality(hll_union_agg(eligible_uu)) from performancedata where ts_date>='2019-08-01' and ...
0
votes
1answer
42 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
0
votes
1answer
22 views

Long running READ queries stuck in “idle in transaction”

I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data ...
4
votes
1answer
455 views

Disk usage of integer column vs boolean column in Postgres

I saw this question Bit vs. Boolean columns. I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 ...
0
votes
4answers
66 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
1answer
36 views

Improve speed of COPY COMMAND in postgres

I would like to understand if increasing work_mem does help improving the speed of the COPY command. Does COPY use work_mem or maint_work_mem extensively?
0
votes
0answers
25 views

PostgreSQL managed by AWS RDS - rdsadmin maxusedtransaction ID not shrinking

We are using AWS RDS Postgres. We reached the 200 million limit on one of the DB and triggered VACUUM FREEZE which took down the instance for roughly 40mins however the XID didn't shrink at all. We ...
0
votes
1answer
44 views

How to make PG query faster even when it is using indices

I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question ...
3
votes
3answers
80 views

Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK ...
1
vote
1answer
27 views

Optimize postgresql query with multiple subqueries

I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high). Table ...
0
votes
3answers
83 views

Array containment queries in Postgres

Question on GIN index on array. I have 2 million rows in a work table.And I need to find work that a user can do, based on the skills he has and also the highest priority work. User always will have ...
0
votes
1answer
45 views

Efficient SELECT for array of tuples in PostgreSQL

Assume I have a table records with the following structure id (unique int) updated (timestamp) I have an input_array with values [[id_1, timestamp_1], [id_4, timestamp_4], ...]. I'll refer to each ...
0
votes
1answer
56 views

Optimize a postgresql database view query

Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum): Client has_many Deals ...
2
votes
1answer
37 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 ...
1
vote
1answer
151 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL WHERE l....
0
votes
1answer
51 views

How to deal with different badges in Postgres Explain Visualizer?

I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
1
vote
1answer
21 views

using pg_prewarm after an ETL load has finished - worth it?

I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left ...
1
vote
1answer
43 views

Postgres query plan shows sequential scan instead of a index scan with on the fly data

My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values. ...
2
votes
0answers
51 views

Optimizing a paginated query with various filters and dynamic sorting

Introduction Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
0
votes
2answers
39 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
0
votes
0answers
11 views

Postgres Database is overflow and Postgres Service not Starting

I am using the Linux file system. There one device which has 4GB emmc and we allocated 1.4 GB for the user. see below the output of df -h. We store the database in mmc1 partition, I Have a Drive of ...
0
votes
3answers
116 views

Postgresql 11 - slow insert performance when table reaches ~5 million records

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD). It seems to be a I/O problem, since there are free memory and processing ...
1
vote
2answers
56 views

How to find resource intensive SQL operations in Postgresql? High CPU usage spike

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. ...
0
votes
0answers
27 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
0
votes
1answer
32 views

Indexes for combined time and location data

I have a multi-TB table with geolocation data for a certain item id and timestamp. It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
0
votes
0answers
34 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
1
vote
1answer
41 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
2
votes
1answer
76 views

How do I get PostgreSQL FDW to push down the LIMIT to the (single) backend server?

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers: CREATE TABLE my_big_table ( user_id bigint NOT NULL, serial bigint NOT NULL, -- ...
0
votes
1answer
137 views

Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)

I've got the very same single-table definition in both PostgreSQL 11.4 and MariaDB 10.4.6 (InnoDB) and I'm seeing a significant difference in INSERT performance I cannot explain for PostgreSQL. My ...
0
votes
0answers
31 views

postgresql query performance issue with timestamp and order by filter

I have table definition as following. CREATE TABLE monmgr_pg.order_staged_events ( event_id varchar(64) NOT NULL, order_number varchar(40) NOT NULL, oms_order_key varchar(64) NOT NULL, ...
0
votes
1answer
47 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
0
votes
1answer
33 views

Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query: SELECT * FROM category_member ORDER BY (p::float8/q) DESC LIMIT 10; I am ordering ...
2
votes
1answer
39 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
41 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
0
votes
1answer
40 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
0
votes
1answer
9 views

Postgres record locking issue - could asynchronous UPDATES be a solution?

Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table? Here’s the issue. I have two process, both of which access the same table. Process “A” loads vast quantities of ...
0
votes
0answers
31 views

Advantage and Disadvantage of Partitioning by some of values of Primary Key

I'm going to implement/deploy a table like this: CREATE TABLE docref ( namespace VARCHAR(50), key VARCHAR(50), time_order INT, state SMALLINT, hash TEXT, ...
1
vote
1answer
30 views

Optimize a slow running Postgres SQL query

I have a SQL optimization question. Raw SQL SELECT releases.id AS release_id, releases.name AS release_name, releases.label_id AS release_label_id, releases.provider_id AS release_provider_id, ...
1
vote
2answers
66 views

Postgres: why did this trigram index slow down regexp queries?

I have a TEXT keyvalues column in Postgres: select * from test5 limit 5; id | keyvalues ----+------------------------------------------------------ 1 | ^ first 1 | second 3 ...
3
votes
2answers
24 views

Optimizing PostgreSQL where clause with OR

I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
1
vote
2answers
52 views

Why does a query take so much longer for 1 additional identical join?

I am trying to figure out why with the query below, adding the last join takes so much longer. If I run it with the last join commented out ( level 3) it runs in less than a second. Adding the 3rd ...
1
vote
1answer
96 views

Is this database fragmentation or something else? What can be done?

I have the table below in PostgreSQL (automagically generated with insertions using Marten): create table mt_doc_order ( id uuid ...
0
votes
1answer
44 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
3
votes
2answers
70 views

Bad performance on recursive query in PostgreSQL

I have bad performance with recursive query in PosgreSQL. psql --version psql (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) The idea is to have tree-like structure of comments. So when parent_id is ...
0
votes
0answers
94 views

Does keyset/cursor pagination scale query performance better than offset pagination?

I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow. Naturally, I used offset pagination because the library I am using ...
2
votes
1answer
82 views

Slow performance on postgres query with count/sort

I've inherited an Django/postgres app from another developer and I'm finding many of the queries are very slow. I've spent a lot of time correctly organising/normalising the data which was not done ...
0
votes
1answer
31 views

Progressive child tables scan in PostgreSQL?

We have append-only events table where events from different devices are being collected. We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions. Data is partitioned using ...