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
36 views
Speed up INSERT of 1 million+ rows into Postgres via R using COPY?
I would like to bulk-INSERT/UPSERT a moderately large amount of rows to a postgreSQL database using R. In order to do so I am preparing a multi-row INSERT string using R.
query <- sprintf("BEGIN;...
-1
votes
1answer
44 views
Proper indexation settings for search in large PostgreSQL table
Trying to get search results from database as fast as possible. The problem is, even after creating index for every field that search goes on, results coming back from PostgreSQL server are insanely ...
0
votes
2answers
78 views
Why is FOR <query> LOOP is so much slower than basic query?
I write very simple plpgsql function which fetches each row from 25mln rows table and compare with prev row. If two sibling rows have equal "AOGUID" column they are returned.
CREATE or replace ...
1
vote
0answers
50 views
node-postgres simple SELECT is becoming extremely slow (PostgreSQL)
I'm developing an server-side app for mobile game backed with postgresql and I'm using pg with Knex("pg": "6.1.2" and "knex": "0.12.6"). Not so long ago I faced a problem with select perfomance ...
1
vote
0answers
75 views
Query takes 60 times as long after change of value
I try to run 2 queries, difference only in where clause where I change state from 'CA' to 'FL'. And execution time increased 60-fold Can You point me in right way, why two symbols changes query time ...
0
votes
1answer
86 views
Postgres slow running delete query
We have a table which has just over 62k rows. We are running a very simple delete query on it which takes 45 minutes to complete:
DELETE FROM myTable WHERE createdtime < '2017-03-07 05:00:00.000'
...
0
votes
1answer
40 views
PostgreSQL select distinct with like condition
I have large table with few millions of records. Each record contain type, which came from outer source. I know that number of types is around 100 - 200.
I need to get subset of types for search ...
0
votes
1answer
51 views
Efficient subquery for aggregate time series
I want to build a time series daily from a certain date and calculate a few statistics for each day. However this query is very slow... Any way to speed it up? (for example, select the table once in ...
1
vote
1answer
31 views
Reverse string with leading wildcard scan in Postgres
SQL Server: Index columns used in like?
I've tried using the query method in the link above with Postgres (0.3ms improvement), it seems to only work with MySQL (10x faster).
MYSQL
User Load (0.4ms) ...
0
votes
2answers
91 views
Sqlx WHERE IN query with long list
I'm using sqlx to perform a query in my Go code. The query has a long list of values (~10,000) that I need to filter on in the WHERE IN (?) clause. This causes a huge slowdown in performance. How can ...
go
0
votes
0answers
40 views
EXPLAIN (BUFFER,ANALYZE) understading
I am using PostgreSQL 9.3 Can some one help me what i can do to improve this. The max number of records in db table is approx 5000 to 7000 varies daily basis. shared_buffers setting in PGsql DB is =...
0
votes
1answer
62 views
Is there a faster way than 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'
I've got the following database table:
date a b c d add result
23.02.07 A B C1 D1 1 1
24.02.07 A B C1 D1 0 1
25.02.07 A B C1 D1 1 2
26.02.07 A B ...
0
votes
2answers
85 views
Refactoring slow SQL query
I currently have this very very slow query:
SELECT generators.id AS generator_id, COUNT(*) AS cnt
FROM generator_rows
JOIN generators ON generators.id = generator_rows.generator_id
WHERE
generators....
0
votes
1answer
52 views
postgres not using index
There are lots of questions on this topic, but all of them seem to be more complex cases than what I'm looking at at the moment and the answers don't seem applicable.
OHDSI=> \d record_counts
...
-4
votes
1answer
45 views
Optimize Postgres TOP-n query
Table with two columns (transaction_id, user_id), both with index. Approx 10M records in table.
transaction_id is unique
transaction_id count on user_id varies from very few to thousands.
What I ...
1
vote
1answer
49 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 ...
0
votes
1answer
76 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
1answer
70 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
vote
1answer
75 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
134 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
158 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
143 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
18 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
85 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
53 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
33 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 ...
1
vote
2answers
51 views
Postgresql IN operator Performance: List vs Subquery
For a list of ~700 ids the query performance is over 20x slower than passing a subquery that returns those 700 ids. It should be the opposite.
e.g. (first query takes under 400ms, the later 9600 ms)
...
2
votes
1answer
79 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
27 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
22 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
55 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
74 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
32 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
55 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
57 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
47 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
144 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
78 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
62 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
69 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
66 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
43 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
61 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
59 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
114 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
59 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
29 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
79 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 ...
