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.
0
votes
1answer
26 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
48 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
29 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
53 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 ...
2
votes
1answer
57 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
74 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
31 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 ...
0
votes
2answers
63 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
70 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
34 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
44 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
85 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 ...
2
votes
2answers
76 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,
...
2
votes
1answer
89 views
Unused index in range of dates query
I have a query that is not using existing indices and I do not understand why.
The table:
mustang=# \d+ bss.amplifier_saturation
Table ...
0
votes
0answers
49 views
PostgreSQL Performance - Many Large Databases
I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
1
vote
3answers
78 views
Slow query when adding additional where clause
Here's my setup (Postgres 9.3)
Posts
project_id
Messages
post_id
kind
updated_at
I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
0
votes
2answers
56 views
What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?
Disclaimer: I am relatively new to PostgreSQL.
I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple:
Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
0
votes
0answers
76 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
81 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
69 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
132 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
28 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
148 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
73 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
135 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
62 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
63 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
39 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
54 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
48 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
86 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
90 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
59 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
156 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
408 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:
...
2
votes
1answer
199 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
2k 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
997 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
53 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
289 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
156 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 ...
2
votes
1answer
792 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
297 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
168 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
138 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
179 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
153 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
128 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
387 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 ...
