Tagged Questions
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.
2
votes
3answers
152 views
+50
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
94 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 ...
13
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
25 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
23 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
40 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
26 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
25 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
69 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
43 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
37 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
91 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
23 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
49 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
24 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
41 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
34 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
41 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
46 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
68 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
9k 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
210 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
179 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
769 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
49 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
17 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
31 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
50 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
99 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
26 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
112 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
56 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
156 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
115 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
22 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 ...
1
vote
1answer
49 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
61 views
How to improve performance when combining two queries with 'IN' operator
I'm using postgres 9.4
select version();
version
-----------------------------------------------...
1
vote
3answers
51 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 ...
0
votes
0answers
121 views
Performance Tuning on Odoo Account Table
i have around 30K's of account records in an Odoo database, and it takes too long even to list those accounts. Analysing the query logs, i found that these kind of query are taking to long to finish (...
15
votes
4answers
7k views
PostgreSQL query runs faster with index scan, but engine chooses hash join
The query:
SELECT "replays_game".*
FROM "replays_game"
INNER JOIN
"replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id"
WHERE "replays_playeringame"."player_id" = 50027
...
14
votes
2answers
13k views
How to understand an EXPLAIN ANALYZE
I am not very familiar with looking at EXPLAIN ANALYZE results, I have a huge problem with my queries being too slow. I have tried to read up on how to interpret results from an explain queries, but ...
4
votes
4answers
199 views
Way to try multiple SELECTs till a result is available?
What if I want to search for a single row in a table with a decrementing precision, e.g. like this:
SELECT * FROM image WHERE name LIKE 'text' AND group_id = 10 LIMIT 1
When this gives me no result,...
2
votes
2answers
92 views
Efficient way to move large number of rows from one table to another new table using postgres
I am using PostgreSQL database for live project. In which, I have one table with 8 columns.
This table contains millions of rows, so to make search faster from table, I want to delete and store old ...
1
vote
2answers
47 views
Selecting single and only single rows from GROUP BY
I have two tables: parcel and structure, with a one-to-many relationship between them: structure.parcel_id points to parcel.id.
I want to select all the single structures. My current solution works, ...
1
vote
3answers
32 views
Trying to Optimize PostgreSQL Nested WHERE IN
I have a Postgres (9.1) customer database similar to:
customers.id
customers.lastname
customers.firstname
invoices.id
invoices.customerid
invoices.total
invoicelines.id
invoicelines.invoiceid
...
1
vote
2answers
65 views
Query Optimization - PostgreSQL
I have a table of 3M rows.
I wanted to retrieve all those rows and do a visualization using dc.js.
Problem I have is, for just a single column it takes about 70 secs.
And If i write my query it ...
0
votes
0answers
76 views
Postgresql index not used in inner join
I am using 9.1
Here is the query:
select a.id,b.flag
from a, b
where b.starting_date>='2002-01-01'::date
and a.zip_code= b.zip_code
and ...
0
votes
1answer
87 views
How to Increase Query Speed
i am trying to speed up some query in postgresql, currently i think is slow, considering that i want to get by date range, currently i have this:
select enc.inputdatetime::date dateMed, enc....
