About PostgreSQL query optimisation. It is essential to include sufficient information in your question as outlined in the tag info. For complex, advanced questions or if you're looking to tune without modifying queries, ask on http://dba.stackexchange.com/ instead.
0
votes
0answers
17 views
Postgres SQL Merge Join horrendously slow
I'm using a Postgres database and trying to optimize the following query:
SELECT DISTINCT catalogite1_.id
FROM cat_catalogitem catalogite1_
INNER JOIN cat_service service2_
...
1
vote
2answers
48 views
Optimizing SQL query on table of 10 million rows: neverending query
I have two tables:
CREATE TABLE routing
(
id integer NOT NULL,
link_geom geometry,
source integer,
target integer,
traveltime_min double precision,
CONSTRAINT routing_pkey PRIMARY KEY (...
2
votes
2answers
869 views
Vastly different query run time in application
I'm having a scaling issue with an application that uses a PostgreSQL 9 backend. I have one table who's size is about 40 million records and growing and the conditional queries against it have slowed ...
0
votes
0answers
54 views
How to update in PostgreSQL a table with millions records?
I need to update 9 columns of a table t1 with nearly 10 millions rows in Postgres. The query I am applying is:
update t1
set
class = t2.class,
direction = t2.direction,
bt = t2.bt,
rf = t2....
0
votes
0answers
21 views
Performant async I/O for relational databases
We are trying to optimize our database requests and also reduce the number of database connections in our Node.js servers to Postgres database.
Do Redis database drivers and Redis database ...
1
vote
3answers
40 views
Speeding up SELECT query response in large PostgreSQL Database (250 million rows)
Using PostgreSQL, I have a very large table of ~250 million rows composed of the following 4 attributes:
CREATE TABLE public.sim_values_english
(
id bigint NOT NULL DEFAULT nextval('...
1
vote
5answers
56 views
Get 10 distinct projects with the latest updates in related tasks
I have two tables in a PostgreSQL 9.5 database:
project
- id
- name
task
- id
- project_id
- name
- updated_at
There are ~ 1000 projects (updated very rarely) and ~ 10 million tasks (...
0
votes
1answer
51 views
last and first value from group
I have a task: get first, last, max, min from each group (by time) of data. My solution works but it is extremely slow because row count in table is about 50 million.
How can i improve performance of ...
0
votes
1answer
27 views
PostgreSQL slow JOIN with CASE statement
In my database I have a table that contains ~3500 records and as a part of more complicated query I've tried to perform inner join on itself using "CASE" condition just as you can see below.
SELECT *
...
0
votes
1answer
46 views
postgres query with IN is very slow
I have a table which has an index on (column A, column B). And I'm running a query that looks like this:
SELECT * FROM table WHERE (A, B) IN ((a_1, b_1), (a_2, b_2), ..., (a_5000, b_5000))
This ...
11
votes
1answer
11k views
Finding similar strings with PostgreSQL quickly
I need to create a ranking of similar strings in a table.
I have the following table
create table names (
name character varying(255)
);
Currently, I'm using pg_trgm module which offers the ...
1
vote
1answer
24 views
Beginner PostgreSQL: Setting up environment for querying large pre-existing database
I'm brand spanking new to relational databases, and need help setting up a basic working environment for querying a large (pre-existing) database. I've connected to our remote server through PGAdmin, ...
-1
votes
1answer
33 views
Postgresql 9.5 Performance Issue
I have a PostgreSQL 9.5 instance running on Windows 8 machine with 4GB of RAM.This server is mainly used for inserting/updating large amounts of data via copy/insert/update commands, and seldom for ...
2
votes
3answers
178 views
postgres large table select optimization
I have to extract DB to external DB server for licensed software.
DB has to be Postgres and I cannot change select query from application (cannot change source code).
Table (it has to be 1 table) ...
5
votes
2answers
99 views
Optimizing a row exclusion query
I am designing a mostly read-only database containing 300,000 documents with around 50,000 distinct tags, with each document having 15 tags on average. For now, the only query I care about is ...
14
votes
4answers
11k views
Postgres query optimization (forcing an index scan)
Below is my query. I am trying to get it to use an index scan, but it will only seq scan.
By the way the metric_data table has 130 million rows. The metrics table has about 2000 rows.
metric_data ...
1
vote
1answer
34 views
Postgres table select query is too slow
Am having a gps-tracking application.It has a table named gps_vehicle_data where incoming gps data are stored frequently. I query this table at frequent intervals to process it as it contains just raw ...
0
votes
0answers
28 views
Postgres PostGIS left join query performance
The db structure is more or less like this and cant be modified. I can only add some indexes but cant set for example foreign keys:
CREATE TABLE adresspoint
(
id character varying(3999) NOT ...
0
votes
1answer
42 views
Large query time for analytics purposes
I'm analyzing a 50GB (16M rows, 15 columns) table using PostgreSQL 9.4 using only select queries (no inset\updates). My server has 4 virtual sockets and 16GB RAM.
A typical query i'm using:
select y,...
0
votes
0answers
28 views
Postgresql performance tuning
The following rather complex query
select adresse.plz, gemeinde.gemeindename, ortschaft.ortsname, strasse.strassenname, adresse.hausnrzahl1, ST_Y(adresse.latlong), ST_X(adresse.latlong)
from adresse
...
1
vote
1answer
45 views
Should I partition my postgres tables?
I am storing some stock data in a Postgres 9.5 database with the following format for my tables with a primary key on (datetime, symbol):
symbol (varchar[30]),
datetime (timestamptz),
value (double ...
1
vote
0answers
75 views
Optimize Window queries in Postgresql
I have a products table with approximately 17,000,000 records.
CREATE TABLE vendor_prices (
id serial PRIMARY KEY,
vendor integer NOT NULL,
sku character varying(25) NOT NULL,
category_name ...
0
votes
3answers
45 views
How make this filter sargeable or improve index
vzla_seg are the road network segments. Each segment have his general azimuth. COUNT(*) = 3.849.834
CREATE TABLE vzla_seg (`azimuth` int);
INSERT INTO vzla_seg (`azimuth`)
VALUES
(330), (...
0
votes
1answer
40 views
Plan with higher cost runs faster
I am trying to reproduce code example from Markus Winand website (Slow Indexes Part II)
Test case:
Create a table that is not analyzed and introduce an index that will
work slower than full table ...
1
vote
3answers
100 views
Count on join of big tables with conditions is slow
This query had reasonable times when the table was small. I'm trying to identify what's the bottleneck, but I'm not sure how to analyze the EXPLAIN results.
SELECT
COUNT(*)
FROM ...
0
votes
0answers
28 views
postgresql seq scan sometimes very slow. restart of postgres solves it for a while
I have having a problem where the same query on a table sometimes takes several seconds and most times completes in a few milliseconds. Restart of Postgres seems to resolve the issue and keep it away ...
1
vote
1answer
64 views
Can PostgreSQL array be optimized for join?
I see that Postgres array is good for performance if the array's element is the data itself, e.g., tag
http://shon.github.io/2015/12/21/postgres_array_performance.html
How about if I use array as a ...
0
votes
0answers
25 views
postgresql ignore index after vacuum
I have the following index:
CREATE INDEX in_table_1
ON table_1
USING btree
((COALESCE(col_1, 30001231::bigint)), col_2);
and the following select
select * from tabela_1
where coalesce(col_1, ...
0
votes
2answers
43 views
Slow SQL query using OR
The following query is 10x faster if I delete the "OR" clause.
WITH RECURSIVE toeng(lang1, english, syn, enid) AS
(SELECT lang1.word, english.word, english.synonym, english.id
FROM lang1 INNER JOIN ...
1
vote
2answers
41 views
Get COUNT() result within time limit
Is there a way in PostgreSQL to abort execution of COUNT(*) statement and return its current result?
I would like to run:
SELECT COUNT(*) FROM table WHERE something=x;
Some queries are completed ...
1
vote
0answers
46 views
Extremely slow query in PostgreSQL (order by multi col)
Please let me know if you need the table definitions. As I'm sure is obvious, I have several tables holding information about a user (partydevicestatus, groupgps) each have a foreign key relationship ...
1
vote
1answer
49 views
postgres query optimization when always false where condition is present
We have a query which goes like this.
SELECT DISTINCT table_a.userid AS userId,
table_a.screenname AS screenName,
FROM table_a
LEFT JOIN table_b
ON ( ...
1
vote
2answers
74 views
Multiple correlated subqueries with different conditions to same table
I have two tables:
orders
| id | item_id | quantity | ordered_on |
|----|---------|----------|------------|
| 1 | 1 | 2 | 2016-03-09 |
| 2 | 1 | 2 | 2016-03-12 |
| 3 | ...
12
votes
3answers
10k views
Optimize GROUP BY query to retrieve latest record per user
I have the following table (simplified form) in Postgres 9.2
CREATE TABLE user_msg_log (
aggr_date DATE,
user_id INTEGER,
running_total INTEGER
);
It contains up to one record per user ...
16
votes
2answers
211 views
Best performance in sampling repeated value from a grouped column
This question is about the functionality of first_value(), using another function or workaround.
It is also about "little gain in performance" in big tables. To use eg. max() in the explained context ...
2
votes
2answers
225 views
Why could PostgreSQL 9.5's CUBE, ROLLUP and GROUPING SETS be slower than equivalent UNION?
I've anticipated new PostgreSQL 9.5 features very much, and going to upgrade our database very soon. But I was quite surprised when I found that
SELECT col1, col2, count(*), grouping(col1,col2)
...
5
votes
1answer
785 views
Add datetime constraint to a PostgreSQL multi-column partial index
I've got a PostgreSQL table called queries_query, which has many columns.
Two of these columns, created and user_sid, are frequently used together in SQL queries by my application to determine how ...
1
vote
2answers
52 views
Sub query on large table with join extremely slow
I have the following query,
SELECT * FROM users,
(SELECT *
FROM mastery
WHERE champion_rank = 1
ORDER BY global_rank ASC
LIMIT 3) as ranks
WHERE users.id = ranks.user_id
Mastery has 22M rows ...
0
votes
1answer
21 views
Optimizing date queries in postgresql
I'm having a hard time to optimizing queries on a very big table. Basically all of the them filter the set of results by the date:
SELECT FROM bigtable WHERE date >= '2015-01-01' AND date <= '...
-2
votes
1answer
32 views
Poor performing postgres sql
Here's my sql, followed by the explanation. I need to improve the performance. Any ideas?
PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
...
0
votes
1answer
42 views
Postgres using slower index with small limit
I have an interesting conundrum.
I have a few different queries that slow down significantly in certain situations.
This one is fast:
SELECT
"posts".*
FROM "posts"
WHERE "posts"."source_id" IN (...
2
votes
0answers
53 views
PostgreSQL query plan with LIMIT
If the actual result of a query would join a lot of data (like when searchterm is short or a frequent city name, below), limiting the output of that query with limit does not improve execution time ...
0
votes
0answers
101 views
Postgres poor performance “in-clause”
I have this query:
with serie as (
select to_char(kj, 'yyyymmdd')::numeric
from generate_series('2016-02-06 01:56:00','2016-02-06 23:57:00', '1 day'::interval) kj
)
select col1,col2,...
0
votes
0answers
27 views
Implement a regular serie in postgreSQL
Let's assume I have the following table:
time| id| value
1| 1| 1
3| 1| 1
1| 2| 2
The result of selecting a regular series would be:
time| id| value
1| 1| 1
2| 1|...
2
votes
3answers
113 views
Column Copy and Update vs. Column Create and Insert
I have a table with 32 Million rows and 31 columns in PostgreSQL 9.2.10. I am altering the table by adding columns with updated values.
For example, if the initial table is:
id initial_color
-- ...
0
votes
2answers
66 views
Postgres not using index for range query in partitioned table
I found that Postgres is not using an index for a range query on a partitioned table.
The parent table and its partitions have their date column indexed using btree.
A query like this:
select * ...
1
vote
1answer
246 views
Improve performance on SQL query with Nested Loop - PostgreSQL
I am using PostgreSQL and I have a weird problem with my SQL query. Depending on wich date paramter I'm using. My request doesn't do the same operation.
This is my working query :
SELECT DISTINCT ...
4
votes
2answers
119 views
Why does a slight change in the search term slow down the query so much?
I have the following query in PostgreSQL (9.5.1):
select e.id, (select count(id) from imgitem ii where ii.tabid = e.id and ii.tab = 'esp') as imgs,
e.ano, e.mes, e.dia, cast(cast(e.ano as varchar(4))...
0
votes
1answer
25 views
PostgreSQL: create two functions or add a parameter to an existing function?
Suppose I have a not trivial query to return a list of places from a given name piece e.g. "Rio" would return both "Rio de Janeiro" and "Rio Negro". But in one place I would need a short version like "...
0
votes
1answer
109 views
How to optimize query postgres
I am running the following query:
SELECT fat.*
FROM Table1 fat
LEFT JOIN modo_captura mc ON mc.id = fat.modo_captura_id
INNER JOIN loja lj ON lj.id = fat.loja_id
INNER ...
