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
2answers
40 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 ...
0
votes
1answer
69 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
23 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
38 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
40 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
48 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
40 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
43 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
55 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
53 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
57 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
53 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
25 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
52 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
28 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
52 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
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
40 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
193 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
102 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
38 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
31 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
46 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
68 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
76 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
42 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
30 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
102 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
69 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
44 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
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
2answers
75 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 | ...
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 in ...
1
vote
2answers
53 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
...
1
vote
1answer
50 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 ( ...
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
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| ...
0
votes
0answers
104 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
2answers
77 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
312 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
120 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))...
1
vote
1answer
59 views
Bidirectional index
Is there way for bidirectional index (for effective ordering ASC/DESC)?
There is some table:
CREATE TABLE t1(
id VARCHAR NOT NULL PRIMARY KEY,
d TIMESTAMP)
and there is DESC index for d ...
1
vote
4answers
65 views
How to improve performance when combining two queries with 'IN' operator
I'm using postgres 9.4
select version();
version
-----------------------------------------------...
1
vote
3answers
53 views
Query taking a very long time
I have the following query to return userids that are not in the message_log table
select * from likes where userid not in(select to_id from message_log)
I have an index on the userid column in the ...
