PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
1
vote
1answer
20 views
Postgresql equivalent to sqlite pragma
What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql.
pragma synchronous = OFF;
pragma journal_mode = OFF;
pragma count_changes = OFF;
...
5
votes
2answers
458 views
How to make this query use my multicolumn index?
Currently, I have a view which is defined like this:
View "public.customer_list"
Column | Type | Modifiers | Storage | Description
...
3
votes
1answer
33 views
Why is my tsv index not being used?
I'm trying to get the postgres full-text-search facility functional.
I have two tables, one I created just for testing, and the actual one I want to be able to search:
Test Table:
webarchive=# \d ...
13
votes
2answers
2k views
Are WHERE clauses applied in the order they are written?
I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.
select 1
from workdays day
where ...
2
votes
3answers
83 views
Performance of rare SELECT vs. frequent INSERT in timeseries data
I have a simple timeseries table
movement_history (
data_id serial,
item_id character varying (8),
event_time timestamp without timezone,
location_id character varying (7),
...
2
votes
0answers
29 views
PostgreSQL count() works extremely slow on replication slave
I'm using postgres replication. I have one master and 2 slaves.
I have table first with 1 500 000 rows. If I perform select count(*) from first on master, it takes less than a second, but on both ...
2
votes
1answer
28 views
PostgreSQL indices on multi fields queries
I have the following table:
id message_read notification_sent send_date text version recipient sender
-- ------------ ----------------- ...
4
votes
2answers
115 views
What causes large INSERT to slow down and disk usage to explode?
I have a table of about 3.1 million rows with the following definition and indexes:
CREATE TABLE digiroad_liikenne_elementti (
ogc_fid serial NOT NULL,
wkb_geometry geometry(Geometry,4258),
...
3
votes
1answer
43 views
PostgreSQL fsync off for a single database
I have a write-intensive database stored in PostgreSQL v9.4.4 and it causes IO on my device. I'd like to switch fsync off for only a single database, not PostgreSQL server wide. (I accept that it can ...
5
votes
1answer
41 views
How to properly implement compound greatest-n filtering
Yep, more greatest-n-per-group questions.
Given the a table releases with the following columns:
id | primary key |
volume | double precision |
chapter ...
2
votes
2answers
84 views
PostgreSQL query very slow when subquery added
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;
EXPLAIN ANALYZE output:
Limit ...
3
votes
1answer
44 views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers | ...
1
vote
0answers
38 views
3 tables joins performance issue
DB: Postgres 9.4
schema:
table a:
id, score
table ab:
a_id, b_id, b_type
table b:
id, name, type
I am trying to get top 10 result in the following format:
name, count(group by name), ...
4
votes
2answers
28 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
85 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
25 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 ...
2
votes
0answers
38 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
38 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
40 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
85 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 ...
4
votes
1answer
31 views
To minimize Cache misses in PostgreSQL?
You can calculate cache misses as described here.
However, I am interested in how to minimize the phenomenon in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, ...
1
vote
1answer
71 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
64 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
47 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
115 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
25 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
34 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
51 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
81 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
60 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
180 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
250 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
71 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
59 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
66 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
51 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 ...
8
votes
5answers
8k 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
107 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
66 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
53 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
43 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
109 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
86 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
391 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
228 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
202 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
185 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
1answer
108 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
160 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 ...
