PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
1
vote
2answers
35 views
trgm ILIKE index not working - PostgreSQL 9.2
I've got a simple but slow query:
SELECT DISTINCT title
FROM ja_jobs WHERE title ILIKE '%RYAN WER%'
and clientid = 31239 AND time_job > 1457826264
order BY title
...
4
votes
2answers
198 views
Optimize query to find top N users who commented on a post
Problem
Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
5
votes
1answer
32 views
Indexes: integer vs string performance if the number of nodes in the index is the same
I hope this is the right place to ask!
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
1
vote
0answers
14 views
Profiling postgres with gprof: no data/gprof folder and 'no time accumulated'
I'm attempting to profile Postgres with gprof. A gmon.out file successfully gets created in both the data directory and in the working directory where I launched the client (pgbench) process from.
...
2
votes
2answers
58 views
Filtering UNION ALL result is much slower than filtering each subquery
(edit: see end for a simpler example)
I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
1
vote
0answers
34 views
Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
...
1
vote
1answer
37 views
Most efficient way to retrieve data by timestamps
I'm using PostgreSQL 9.2.8.
I have table like:
CREATE TABLE foo
(
foo_date timestamp without time zone NOT NULL,
-- other columns, constraints
)
This table contains about 4.000.000 rows. ...
1
vote
1answer
37 views
Slow Query Postgres 9.2
I've got the following query:
SELECT
j.id,
concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
c.email AS client_email,
...
1
vote
1answer
25 views
PostgreSQL streaming replication stops updating
I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive. Until this morning, I never had more than a minute or ...
7
votes
2answers
174 views
Looking for a simpler alternative to a recursive query
The actual query is more involved, but the problem I'm facing can be distilled to this:
A query to filter a rowset of monotonically increasing integers so that - in the final result set, ...
1
vote
1answer
130 views
Postgres sharding installation with citusdb
We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end.
We are using a 16*2.4 GHz processors with ...
2
votes
2answers
95 views
Eliminate duplicates and optimize performance for big count and small LIMIT
I have a big query in my Postgres 9.1 database:
SELECT *,
count(*) OVER () AS full_count
FROM (
SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime,
...
0
votes
0answers
72 views
Seq Scan faster than Index Only Scan
How can it happen, that a seq scan is faster than an index only scan, even no heap fetches are needed?
I have a table with 100 million rows, selecting all of them with a time range query.
...
1
vote
1answer
13 views
Is there a limit to the number of database connections from a single machine in PostgreSQL?
We are planning on setting up multiple front ends behind a load balancer.
I think our database can handle the load because I'm currently measuring a database TPS of about 450 for approximately ...
2
votes
2answers
54 views
Index for OR operator: a=x or b=x
I have table with three integer columns id, a and b.
I want to get all records where a or b match specified parameter ordered by id:
select id, a, b from t where a=x or b=x order by id
Note that x ...
1
vote
0answers
27 views
Postgres query optimization: why could two very similar join tables have vastly different querying times?
I'm struggling with optimizing the performance of a single query, which is sometimes taking up to 180s for certain tenants. When I re-run this later in pgadmin on the production database server (using ...
0
votes
0answers
42 views
Moving slow sub-query to With clause
I am trying to optimize this query :
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE
(
a-alias.created_at_minute_id >=
(floor(extract(epoch from(
select ...
3
votes
1answer
57 views
SELECT very slow when no results and a LIMIT is specified
I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified.
If the number of results is ...
5
votes
1answer
88 views
Sub-select takes “ages” - EXCEPT much faster
Script to create the tables
DROP TABLE IF EXISTS history;
CREATE TABLE history (
id integer NOT NULL,
ticket_id integer NOT NULL);
ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
1
vote
0answers
15 views
Very different query plans with and without LIMIT in PostgreSQL.
I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
2
votes
1answer
63 views
Why is index not used for IN expression with unnested array
I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you.
I have two tables, users and ...
0
votes
0answers
22 views
Performance approach to PostgreSQL queries sequencing worth it?
So I have a job that processes tables for each tenant in our application
Loop tenant_1 to tenant_100
fork own process
process table A
process table B
end
end
so what happens is, there is 100 ...
2
votes
1answer
77 views
How to debug when OS kills postgres for high memory usage
I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day).
This is the message from dmesg
Out of memory: Kill process 1020 (postgres) score 64 or ...
6
votes
2answers
127 views
Exists subselect vs inner join?
I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
1
vote
1answer
35 views
How to optimize view with group by
Have view with query :
SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id,
sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, ...
3
votes
1answer
119 views
Speeding up a GROUP BY, HAVING COUNT query
I'm trying to speed up this query in Postgres 9.4:
SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) ...
0
votes
1answer
46 views
Query bytea column using prefix
Assuming I have a table with a bytea column called data on a table called t, how can I:
Write a query that returns all rows which have a specific prefix on their data column.
Index the query.
I am ...
3
votes
1answer
71 views
How to read postgres.log output?
I need to improve my database performance, in order to achieve that, I enabled the log in my postgresql.conf:
log_statement = 'all'
log_connections = on
log_disconnections = on
Why do I have 3 ...
1
vote
1answer
34 views
How to influence the planner on Postgresql
I have the following:
a complex query, 'match', that returns about 200 rows (ids)
a huge table, 'usernames', indexed on id that maps them to usernames
When I try to left join the query (1) with ...
1
vote
2answers
100 views
For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?
When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on hot_standby_feedback or set the max_standby_*_delay ...
14
votes
1answer
402 views
Is it worth it to run VACUUM on a table that only receives INSERTs?
In a 2015 re:Invent talk, AWS mentioned that vacuum should be run not only after updates or deletes but also after inserts. Here is the relevant part of the talk:
...
0
votes
1answer
43 views
TEMP table alternatives for pl/pgsql
I am trying to emulate MERGE behavior with pl/pgsql:
-- Generate the data from funtion
CREATE TEMP TABLE temp_x (id int, id2 int, data text, created_at timestamp, updated_at timestamp) ON COMMIT ...
0
votes
2answers
57 views
Measure CPU and Memory usage while running pgbench
what is the best way to capture the usage of CPU and memory while running pgbench? The only way I can think of is to read the info from top header.
what I do now
I run top command by print it out ...
0
votes
0answers
53 views
How does the PostgreSQL query planner estimate the aggregated rows count?
During an SQL optimization session I ran into the following problem (I won't post the full query and its plan as they are quite large):
The plan:
->__SOME_OTHER_NODES__
-> ...
3
votes
1answer
84 views
Using ILIKE with unaccent and with only right end wildcard
I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
4
votes
1answer
89 views
Debug query on big table that is sometimes slow
I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries ...
4
votes
1answer
90 views
pgAdmin is extremely slow on any remote operation
I run this query from my local pgAdmin connecting remotely to our dev server:
select * from users order by random() limit 1;
it hangs for 17 seconds and shows
Total query runtime: 148 ms.
1 row ...
3
votes
1answer
42 views
Postgres reduce by key if key is not null
I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with ...
0
votes
1answer
26 views
Is it possible to set order in which inherited tables a scanned in postgresql?
I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125.
When I run explain ...
2
votes
0answers
50 views
Insert performance
I am having trouble with Posgresql insert performance. There are 2 tables, documents and words, where documents is essentially a mapping from an external path to a surrogate id, which is foreign ...
1
vote
1answer
52 views
Why does this PostGIS query take so long to run?
I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing ...
1
vote
1answer
110 views
Postgres 9.5 foreign table inheritance not using indexes
In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
2
votes
1answer
135 views
PostgreSQL 9.4 query seems very slow
I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
0
votes
0answers
16 views
PostgreSQL 9.4 CTE to count associated records using 2 columns
I need to count child records and cache the count in the parent record using a PostgreSQL 9.4 CTE. I have an Audit table and an Order table. The Audit table will keep a count of how many Orders ...
2
votes
2answers
103 views
Retrieving changed rows only from a table with 10^7 rows
I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well.
I've ...
4
votes
1answer
144 views
PostgreSQL seems to create inefficient plans in simple conditional joins
Consider these two queries:
SELECT
t1.id, *
FROM
t1
INNER JOIN
t2 ON t1.id = t2.id
where t1.id > -9223372036513411363;
And:
SELECT
t1.id, *
FROM
t1
INNER JOIN
t2 ON ...
2
votes
1answer
80 views
PostgreSQL not using index during count(*)
I have a COUNT(*) query in PostgreSQL that runs often, and looks like:
SELECT COUNT(*)
FROM customer
WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);
This query ...
1
vote
0answers
23 views
PostgreSQL: Improve system performance, KNN modification with GiST Index
In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on ...
1
vote
0answers
55 views
PostgreSQL doesn't use index on inherited tables
I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS:
an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
0
votes
0answers
37 views
How to calculate percentage of multiple values of columns in PostgreSQL?
I've the table named reviews which shows the hotels' services ratings given by guests who stayed at respective hotel as shown below
dtime prop clean staff location amenety travel_type
...
