The postgresql-performance tag has no wiki summary.
1
vote
0answers
21 views
Postgres Performance Over Group by with MAX and MIN
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id ...
1
vote
1answer
44 views
Efficient way to insert/update/delete table records from complex query in Postgres 9.x
I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and ...
1
vote
1answer
93 views
PostgreSQL's Query Hierarchical data poor performance
Badly need some technical assistance on this query performance issue I'm working on.
Summary of the data structure is:
1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...
2
votes
1answer
74 views
Extremely long query time after upgrading to Postgres 9.3 from 9.1
We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
1
vote
1answer
98 views
Very slow simple PostgreSQL query on RDS
I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).
This is across a table of 4,152,928 rows..
select sum(some_field) c
from pages
where pages.some_id=123
and ...
1
vote
0answers
34 views
How many Maximum Number of database can be created in single instance of PostgreSQL? [closed]
How many Maximum Number of database can be created in single instance of PostgreSQL 9.x?
0
votes
0answers
71 views
High Availability in PostgreSQL with automated failover
I am currently working on designing an architecture for High Availability in PostgreSQL for a database of approximately 5TB in size. Also, I would like to re-direct my reads to a slave node(preferably ...
3
votes
1answer
399 views
Postgresql subquery speed much slower than individual queries
In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal ...
1
vote
1answer
129 views
log_min_duration_statement setting is ignored
I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows.
I have 2 databases that are actively in use and they are used from a remote server.
I ...
0
votes
1answer
126 views
PostgreSQL - Return unique combinations of columns based on where clause
This is quite a long question, please bear with me.
So I'd like to first explain I have a database of firewall logs created using the following command:
CREATE TABLE firewall_logs_mapped
(
...
3
votes
1answer
112 views
Postgresql query slowed with table growth
I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt.
Table body_feature:
...
1
vote
1answer
99 views
Optimizing Postgres query
I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.
I've got two indexes on addresses table :
Index on state field
...
1
vote
1answer
87 views
scenario to update or insert big amount of data
I try to find the best scenario to update or insert 1 million contacts in my postgres database.
My actual scenario is:
read contacts 1000 per 1000 and try to insert multiple rows with one request
...
2
votes
0answers
81 views
Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints
If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a ...
0
votes
1answer
263 views
Simple PostgreSQL lookup table is inexplicably slow
I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String).
For speed, I'm not repeatedly storing strings but rather using two tables:
main table triples ...
1
vote
2answers
287 views
Assign values with the := operator
When I assign a variable with
result := title || '', by '' || author;
it is taking more time (approx. 15 sec) to run the function.
However, when I assign the variable with
result = title || '', by ...
2
votes
1answer
307 views
Configuring PostgreSQL to match server configuration
We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram ...
2
votes
2answers
127 views
Scaling beyond 16 concurrent connetions
I've just adopted a Postgres 9.0 database running on RHEL 6.1 on a rather beefy 256 core, 2TB system with ok drive (Hardware RAID 50).
I've started benchmarking the system using pgbench so I can get ...
0
votes
1answer
1k views
pgsql slow query on table of 500 million rows
I have a table that keeps measurements of latencies between nodes running MPI tasks in a large cluster. The table looks like this:
CREATE TABLE latency(
from_rank int,
to_rank int,
from_host ...
-1
votes
1answer
89 views
PostgreSQL select optimization
Can you please explain, how I can optimize select like this:
SELECT * FROM calls WHERE id_temp % 5 = 0 LIMIT 300000
This select is working on table with 50 millions of records.
Thanks a lot and ...
1
vote
2answers
165 views
Prune unused joins
I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case.
I have a bunch of tables with the following structure:
...