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

4
votes
2answers
10 views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
5
votes
3answers
67 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
1
vote
0answers
17 views

Do you need external caching for a Postgres database?

I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
1
vote
0answers
27 views

Optimize PostgreSQL server setting for extremely wide tables

We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM. On this server we have quite atypical data - there are several tables with ...
1
vote
0answers
28 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
1
vote
1answer
36 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...
3
votes
1answer
65 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON ...
1
vote
1answer
47 views

postgres Poor performance on ORDER BY “id” DESC LIMIT 1

I have table items with following schema (in postgres v9.3.5): Column | Type | Modifiers | Storage ...
3
votes
1answer
59 views

Postgres 4x Slower Than It Was

Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why. We have two machines with identical hardware (let's call them A and B): Intel(R) Xeon(R) CPU E5-4640 0 @ ...
1
vote
1answer
45 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
2
votes
2answers
106 views

Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

I am using PostgreSQL 9.4.4. I have a query like this: SELECT COUNT(*) FROM A,B WHERE A.a = B.b a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b By default, ...
2
votes
0answers
24 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 ...
2
votes
1answer
28 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
0
votes
1answer
45 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
3
votes
1answer
69 views

Slow window function query with big table

I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup: CREATE TABLE player_stat ( ...
3
votes
1answer
53 views

Why does it take so long for Postgres to return a sequence number?

I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id. ...
4
votes
2answers
132 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
4
votes
2answers
224 views

How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I have a table like this: CREATE TABLE products ( id serial PRIMARY KEY, category_ids integer[], published boolean NOT NULL, score integer NOT NULL, title varchar NOT NULL); A product ...
2
votes
1answer
55 views

postgresql - how and why indexes are bigger than their tables

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables. Sample output: database_name | database_size | table_name ...
0
votes
0answers
56 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
0
votes
1answer
65 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. ...
0
votes
1answer
50 views

improve long running query

I have this query INSERT INTO interactions( user_id, external_object_id, origin, interaction_type, interaction_sub_type, published_at, origin_id, created_at, updated_at) SELECT ...
7
votes
5answers
5k views

SELECT DISTINCT on multiple columns

Supposing we have a table with four columns (a,b,c,d) of the same data type. Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
4
votes
3answers
85 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
1
vote
1answer
62 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
1
vote
1answer
40 views

Spiky COMMIT results in performance slowdowns with Postgres 8.4

We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our ...
1
vote
1answer
39 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
3
votes
1answer
107 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
2
votes
1answer
75 views

Postgresql performance issues when issuing many small inserts and updates one at a time

I have a production web server with postgresql database. My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in ...
1
vote
2answers
276 views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
2
votes
2answers
172 views

Postgres Slow Queries - Autovacuum frequency

We've noticed the performance of our platform drop in recent weeks so I've run the following: select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables ...
4
votes
2answers
158 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
2
votes
1answer
138 views

how to use index to speed up sorting in postgres

I am using postgres 9.4. The messages has the following schema: messages belongs to feed_id, and has posted_at, also messages can have a parent message (in case of replies). ...
1
vote
0answers
87 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...
4
votes
3answers
141 views

Scalable query for running counts of events within x previous days

I already posted this question on stackoverflow but I thought that I might get a better answer here. I have a table storing millions of events occurring to users: ...
1
vote
2answers
58 views

Slow Select over View

My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k. But I still think it should be faster than ~16 ...
0
votes
0answers
91 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
1answer
37 views

Index for a Postgres query with a sort and an equality

This query is very slow: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Follows" AS "Follow" INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id" WHERE "Follow"."followerId" ...
0
votes
1answer
79 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
1
vote
1answer
44 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT ...
1
vote
1answer
90 views

PostgreSQL isn't using indices in FULL OUTER JOIN

I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I ...
3
votes
1answer
146 views

How do completely empty columns in a large table affect performance?

I have 400 million rows in a Postgres db, and the table has 18 columns: id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without ...
2
votes
2answers
78 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND ...
2
votes
1answer
46 views

Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?

On the PostgreSQL Concurrency With MVCC page, it says: know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation ...
2
votes
2answers
194 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
1
vote
0answers
104 views

Database performs differently on the two same Xeon E3 1270-v3 Quad Core machines [closed]

Configuration: RAM: 16 GB DDR3. HDD: Seagate 500 GB 7200 3Gb/s 3.5 inch SATA - idential disks. Postgres 9.4, tried with 9.4.1 also Windows 2008 R2 Web Tried these tunings: work_mem = 32MB ...
0
votes
1answer
48 views

What is held in memory during the life of a CTE?

Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement? Is all data except RETURNING data discarded at the end of each sub-statement?
2
votes
1answer
391 views

Can PostgreSQL use multiple partial indexes per query?

I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both? If so, will they be loaded sequentially or ...
3
votes
0answers
208 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
3
votes
2answers
190 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...