About PostgreSQL performance optimization. It is essential to include sufficient information in your question as outlined in the tag info. For simple cases and beginners questions, please ask on http://stackoverflow.com/ instead.

learn more… | top users | synonyms

0
votes
0answers
13 views

Are A or D series VMs better for deploying PostgreSQL on Azure?

In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot... Is there a benefit to choosing the SSD option for ...
3
votes
1answer
28 views

Possible reasons for irregularly high query execution time?

The above is a graph showing the average response times per second (including error bars) for an experiment where I simulate 30 clients which are all invoking the same query multiple times. I ran ...
0
votes
1answer
30 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
1
vote
1answer
56 views

Slow max, min query in Postgresql View

I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" ...
0
votes
0answers
23 views

Slow query on large table after new column is added

I have a table with more than 30 million records, and I recently added a new column with type text. ALTER TABLE database.b ADD COLUMN x TEXT; CREATE INDEX CONCURRENTLY idx_x on database.b(a, x); ...
3
votes
1answer
35 views

PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key

If I have a table containing: CREATE TABLE test( id SERIAL PRIMARY KEY, name VARCHAR(200), age INT, data JSONB ); and data column populated with {"name": xxx, "age": yyy}, sometimes ...
1
vote
1answer
43 views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...
2
votes
0answers
56 views

UPDATE/DELETE performance on partitioned table

Hi I'm using table partitioning in my Postgres 9.3. I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table. I see that the constraint_exclusion is ...
0
votes
0answers
36 views

PostgreSQL queries slower than before?

I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with ...
1
vote
1answer
49 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
0
votes
0answers
22 views

PostgreSQL buffer page size limitations - rebuild with larger size?

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error. This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
1
vote
1answer
45 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
2
votes
1answer
36 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
0
votes
1answer
63 views

Query Processing at backend (I/O on disk)

I have a table with 100k rows created on PostgreSQL 9.3 create table demo_bbb ( id numeric NOT NULL, code_bbb character varying, column_02 character varying, column_03 character varying, ...
3
votes
1answer
58 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
1
vote
2answers
49 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
3
votes
4answers
106 views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive ...
1
vote
2answers
158 views

How do I maintain high INSERT-performance on PostgreSQL

I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database. At the core is a table (partitioned by month) which contains a row for each measurement point: ...
1
vote
1answer
148 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
553 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
523 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
46 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?
2
votes
1answer
215 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. ...
0
votes
0answers
135 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 ...
1
vote
1answer
541 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 ...
3
votes
1answer
1k 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 ...
0
votes
1answer
220 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
149 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
125 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 ...
2
votes
0answers
136 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 ...
1
vote
1answer
130 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 ...
1
vote
1answer
117 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 ...
0
votes
1answer
362 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
578 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
424 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 ...
0
votes
1answer
2k 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 ...
2
votes
2answers
157 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 ...
-1
votes
1answer
109 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
190 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: ...
2
votes
1answer
534 views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...