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.
1
vote
1answer
30 views
Alter Table Set Statistics requires table lock
I have run into a case such that Pg always preferring into a sequential scan for a table that has around 70M rows. (Index scan is ideal for that query and i have confirmed it by setting ...
-3
votes
0answers
21 views
sql query ANALYSE
please read this query and help me for improved
SELECT
"rm"."id",
"rm"."tlgId"
FROM "tlg_message" "rm" INNER JOIN
(
SELECT
max("lrm"."id") AS "id",
"lrm"."tlgId"
FROM "...
0
votes
1answer
53 views
Postgresql, slow SELECT query in postgres table
we are having performance issue regarding select data in a Postgres database.
I have two tables, fleet_test and fleet_inspection_test. fleet_test contains 94 columns as key：（fleet_id, usdot, ...
1
vote
2answers
48 views
Most efficient way to query for lat-long rectangle in SQL
I'm currently making consistent queries for a block of land within a given latitude, longitude rectangle. The coordinates are stored as individual double precision values. I've created a single index ...
-1
votes
0answers
25 views
(Performance Postgres) Inner Query for Date = (select max(date))
I have the following query:
select *
from table T
inner join othertable O
on O.date = (select max(date) from othertable)
and O.month = (select max(month) from othertable)
However, this is ...
1
vote
0answers
56 views
Postgresql becomes unresponsible when new index value is added
In my app I have a concept of "seasons" which change discretely over time. All the entities are related to some season. All entities have season based indices as well as some indices on other fields. ...
0
votes
0answers
39 views
Row level security(RLS) performance is significantly slower in postgres.
Description :
Here is the sample demonstration of the performance issue.
We first created two tables , enabled row level security and created policy as well .
Table definition:
create table ...
0
votes
2answers
36 views
Are temporary tables in postgresql visible over all client sessions?
I want to create a temp table so as to be able to join it to a few tables because joining those tables with the content of the proposed temporary table takes a lot of time (fetching the content of the ...
1
vote
2answers
84 views
Slow Postgres 9.3 Queries, again
This is a follow-up to the question at Slow Postgres 9.3 queries.
The new indexes definitely help. But what we're seeing is sometimes queries are much slower in practice than when we run EXPLAIN ...
0
votes
0answers
15 views
optimize migration of table from csv to pg with self join update
I have a CSV of size 1.5GB containing around 11000000 records and I am populating that data to Postgres table
What I have tried so far is:
set local work_mem = '4000MB';
copy truck_dispatch_logs(
...
0
votes
0answers
36 views
Postgresql simple query very slow response time
I have a really simple database that I am using to build out some JSON for Elasticsearch. I'm the only person connected to the database, and only using it for this specific task. Anyway, hitting some ...
0
votes
1answer
34 views
Create postgres index for table with inner join in RubyOnRails
I have an app based on RubyOnRails 4.0. I have two models: Stores and Products. There are about 1.5 million products in the system making it quite slow if I do not use indices properly.
Some basic ...
1
vote
1answer
17 views
Update table from sub query is performing really slow
I am having a tough time solving a SQL problem. I have scoured the internet already and failed to find a solution.
I've provided some context below. I've only included what I think is relevant.
...
0
votes
0answers
27 views
Performance and index usage for function based queries and function calls inside functions
I'm trying to improve the performance of PostgreSQL functions and function calls inside functions.
In our application, PostgreSQL (9.6) queries are totally based on functions. For example, in order ...
2
votes
1answer
34 views
Postgresql BTREE_GIN index with gin_trgm_ops option?
On https://www.postgresql.org/docs/current/static/pgtrgm.html it is explained how special GIN idexes with gin_trgm_ops option can be used to facilitate trigram similarity operator performance.
...
0
votes
1answer
24 views
postgres two column sort low performance
I've got a query that performs multiple joins. I try to get only those positions of each keyword that are latest in results.
Here is the query:
SELECT DISTINCT ON (p.keyword_id)
a.id AS ...
0
votes
0answers
21 views
Optimizing query speed
I have a huge table with 530 millions of rows
chr pos ref alt id_patient
chr1 3 A . 818-16
chr3 57 T A 818-17
... ... ... ... ...
My DB has been created with COLLATION ='C' and ...
1
vote
2answers
48 views
Incremental DISTINCT / GROUP BY operation
I have a simple two-stage SQL query that operators on two tables A and B, where I use a sub-select to retrieve a number of IDs of table A that are stored as foreign keys in B, using a (possibly ...
1
vote
1answer
72 views
Try optimize a specific SQL query
I use this SQL query (PostgreSQL) :
select *
from (select row_number()
over (order by oid1, oid2, oid3)
as row_number, *
from snmpProperties)
as toto
where toto.row_number=(select ...
0
votes
0answers
24 views
Query acceleration
I have the next query I wish to accelerate
SELECT 'C', mpp_designation,periodic_number
FROM (
SELECT mpp_designation,periodic_number
FROM observations_optical_temp
WHERE mp_type = 'C'
GROUP BY ...
1
vote
0answers
41 views
How to efficiently perform equality query on key-value data with duplicate keys allowed?
I have the following situation:
Data = around 400 million (string1, string2, score) tuples
Data size ~ 20gb, doesn't fit in memory.
Data is stored in a file in csv format, and not sorted by any
...
0
votes
0answers
45 views
Optimize the query (may be avoid nested loop)
How can we optimize the following query:
select *
from program_infos pi
join endeavour_organizations seller_organization on seller_organization.id = pi.supplier_id
join endeavour_organizations ...
0
votes
1answer
52 views
PostgreSQL query is not using an index
Enviroment
My PostgreSQL (9.2) schema looks like this:
CREATE TABLE first
(
id_first bigint NOT NULL,
first_date timestamp without time zone NOT NULL,
CONSTRAINT first_pkey PRIMARY KEY (...
0
votes
1answer
45 views
Why this query is so slow? - PostgreSQL - selecting from SERIAL, TIMESTAMP and NUMERIC(6,2)
This is my measurement_pm2_5 table:
CREATE TABLE public.measurement_pm2_5 (
sensor_id SERIAL,
measurement_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
measurement_value NUMERIC(6,2) NOT NULL,
...
1
vote
2answers
69 views
Performance of Postgresql stored procedures/functions in a multi-tenant environment that has one db with many schemata (one for each tenant)
I am new to Postgresql and I am trying to figure out some details about stored procedures (which I think are actually called functions in pgsql) when used in a multiple schema environment.
The ...
0
votes
2answers
67 views
Postgres How to speed up select statement on partitioned table
I'm looking for ways that I can reduce the time to run select statements in my data warehouse.
We are currently running Postgres Enterprise 9.3.4.10 with the intention of upgrading to 9.6 within the ...
1
vote
2answers
55 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 (...
0
votes
0answers
58 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....
1
vote
3answers
61 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('...
0
votes
0answers
31 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
5answers
58 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
55 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
42 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
56 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 ...
1
vote
1answer
27 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
57 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
232 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
104 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 ...
1
vote
1answer
63 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
39 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
49 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
29 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
106 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
80 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
1answer
46 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 ...
0
votes
3answers
46 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
0answers
40 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
3answers
118 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 ...
1
vote
1answer
83 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
26 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, ...
