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
0answers
14 views
performance issues in postgresql [on hold]
I am new to postgresql .I written some procedure and functions .But I donot know are they have any performance issues or not.
So how to check for performance issues in postgresql for stored ...
1
vote
4answers
55 views
How to improve performance when combining two queries with 'IN' operator
I'm using postgres 9.4
select version();
version
...
1
vote
3answers
32 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
14 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
2answers
174 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 ...
1
vote
2answers
53 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
43 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
27 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
...
0
votes
0answers
49 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
83 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, ...
1
vote
1answer
48 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
60 views
How to delete many rows from frequently accessed table
I need to delete the majority (say, 90%) of a very large table (say, 5m rows). The other 10% of this table is frequently read, but not written to.
From "Best way to delete millions of rows by ID", I ...
1
vote
1answer
68 views
Postgresql not using multi-column indexes (btree_gin)
I am having issues making postgres use my multi-column indexes for full search using the btree_gin extension. This is for a search page for articles. The idea behind the use of btree_gin is to be able ...
-1
votes
1answer
21 views
Query performance issue with ST_Intersects()
Following query is taking 3.06min to execute. How to speedup this query? How to use existing index column while executing query?
SELECT samples_collected.talukname,sample_count,TotalSamples from
(
...
1
vote
1answer
44 views
Prevent usage of index for a particular query in Postgres
I have a slow query in a Postgres DB. Using explain analyze, I can see that Postgres makes bitmap index scan on two different indexes followed by bitmap AND on the two resulting sets.
Deleting one of ...
1
vote
2answers
322 views
Optimizing SQL query with multiple joins and grouping (Postgres 9.3)
I've browsed around some other posts and managed to make my queries run a bit faster. However, I've come to a loss as to how to further optimize this query. I'm going to be using it on a website where ...
2
votes
0answers
42 views
PostgreSQL Index Use On Joins
There are numerous posts on the use of indexes, and I'm trying to understand, but I'm hopeful someone can help with my specific issue. I have a table:
CREATE TABLE f4111
(
costcenter_ilmcu ...
0
votes
1answer
36 views
Query plan & perf. variations on another machine
I'm not a PostgreSQL expert, and I've been struggling with this.
I have a rather simple query:
SELECT exists(SELECT 1 FROM res_users_log WHERE create_uid=u.id), count(1)
FROM res_users u
WHERE ...
0
votes
0answers
50 views
PostgreSQL 9.4 Index speed very slow on Select
The box running PostgreSQL 9.4 is a 32 core system at 3.5ghz per core with 128GB or ram with mirrored Samsung pro 850 SSD drives on FreeBSD with ZFS. This is no reason for this poor of performance ...
4
votes
2answers
72 views
Postgres not using index when index scan is much better option
I have a simple query to join two tables that's being really slow. I found out that the query plan does a seq scan on the large table email_activities (~10m rows) while I think using indexes doing ...
2
votes
1answer
49 views
Optimize Postgres Query with Indexes for large amounts of data
I've got a database, posts that has about 20 million rows in it. I'm trying to narrow down the posts for a paginated list using the following query:
SELECT "posts".* FROM "posts"
WHERE ...
1
vote
1answer
22 views
Why am I getting different query plans for the same query (on master / replicated servers)
I am running the same query on a master and replicated server.
The settings on both servers in the postgresql.conf are the same however the master / write server has 32GB of RAM and the replicated / ...
1
vote
0answers
30 views
Degraded SQL Query Speed By Nesting a Single Query inline vs temp table
I have a query of the following, basic form.
SELECT DISTINCT
a.field1,
b.field2,
c.agg_values
FROM a
INNER JOIN b ON a.something = b.something
LEFT JOIN (
SELECT
...
0
votes
1answer
39 views
Postgres jsonb query missing index?
We have the following json documents stored in our PG table (identities) in a jsonb column 'data':
{
"email": {
"main": "[email protected]",
"prefix": "aliasPrefix",
...
0
votes
0answers
32 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 ...
1
vote
1answer
66 views
Slow performance with small table after extreme reduction of size
I have table with approximately 10 million rows, with the id column being primary key.
Then I delete all rows where id > 10. Only 10 rows remain in the table.
Now, when I run the query SELECT id ...
6
votes
2answers
108 views
How do I increase the speed of my Postgres select statement?
I have the following tables:
CREATE TABLE views (
view_id bigint NOT NULL,
usr_id bigint,
ip inet,
referer_id bigint,
country_id integer,
validated smallint,
completed ...
2
votes
2answers
94 views
Performance of DELETE with NOT IN (SELECT …)
I have these two tables and want to delete all authors from ms_author, who are not present in author.
author (1.6M rows)
+-------+-------------+------+-----+-------+
| Field | Type | Null | ...
1
vote
1answer
90 views
PostgreSQL aggregate query is very slow
I have a table, which contains a timestamp column and a source column varchar(20). I insert a couple thousand entries into this table every hour and I would like to show an aggregate on this data. My ...
1
vote
0answers
28 views
Why no index on timestamp column
I've read a lot of articles about date indexing but I can't use index on timestamp properly.
My table:
CREATE TABLE registration
(
id_reg serial NOT NULL,
pass_uid character ...
8
votes
1answer
130 views
Postgresql 9.4 query gets progressively slower when joining TSTZRANGE with &&
I am running a query that gets progressively slower as records are added. Records are added continuously via an automated process (bash calling psql). I would like to correct this bottle neck; ...
0
votes
0answers
56 views
Postgres query performance monitoring on production servers
My goal here is to identify slow queries, which can be done using slow query logs i.e increase the timeout in postgresql.conf to be some 100ms (acceptable query time) and then identify the slow ...
0
votes
0answers
56 views
Long execution time for indexed query in Postgresql
I am using Postgresql 9.2 and I am facing the problem with the index usage.
Consider a quite big table (124M records) with 2 columns: device_id, date.
Device_id is the id of the device that is ...
1
vote
3answers
142 views
Postgres multi-column index (integer, boolean, and array)
I have a Postgres 9.4 database with a table like this:
| id | other_id | current | dn_ids | rank |
...
9
votes
5answers
257 views
Optimize large IN condition for Redshift query
I have a ~2TB fully vacuumed Redshift table with a distkey phash (high cardinality, hundreds of millions of values) and compound sortkeys (phash, last_seen).
When I do a query like:
SELECT
...
3
votes
1answer
36 views
Fewer rows versus fewer columns
I am currently modeling a table schema for PostgreSQL that has a lot of columns and is intended to hold a lot of rows. I don't know if it is faster to have more columns or to split the data into more ...
0
votes
3answers
51 views
Slow query on a large table when using order by
I have a table with ~30M tuples. The table looks like:
id | first_name | last_name | email
-----------------------------------------
1 | foo | bar | [email protected]
Also there are an ...
1
vote
2answers
52 views
Does updating a row with the same value actually update the row?
I have a performance-related question. Let's say I have a user with the first name Michael. Take the following query:
UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123
Will the query ...
0
votes
1answer
51 views
Any way to speed up this sql query?
I have the following Postgres query, the query takes 10 to 50 seconds to execute.
SELECT m.match_id FROM match m
WHERE m.match_id NOT IN(SELECT ml.match_id FROM message_log ml)
AND m.account_id = ?
...
0
votes
2answers
72 views
Hibernate SQL working slow
I am working on Struts2 with Hibernate. For accessing data from database I am using hibernate. I have used following hibernate configuration
<hibernate-configuration>
<session-factory>
...
1
vote
1answer
57 views
PostgreSQL: FULL OUTER JOIN on RANGE datatypes
I have several tables with partially overlapping TSTZRANGE values and I need to JOIN them such that "breakpoints" are created for each UPPER and LOWER boundary for each range.
Some sample data:
...
1
vote
5answers
95 views
Better way than multiple SELECT statements?
I'm creating a web app that displays a pie chart. In order to get all the data for the chart from a PostgreSQL 9.3 database in a single HTTP request, I'm combining multiple SELECT statements with ...
1
vote
1answer
52 views
postgresql - are my indexes or column types slowing down my query?
I have a table I created locally to use some of PG's window functions on a dataset that has about 4 million rows (originally a text file). Each row corresponds to a customer order.
CREATE TABLE ...
0
votes
1answer
68 views
Performance improvement for fetching records from a Table of 10 million records in Postgres DB
I have a analytic table that contains 10 million records and for producing charts i have to fetch records from analytic table. several other tables are also joined to this table and data is fetched ...
3
votes
1answer
159 views
First call of query on big table is surprisingly slow
I have a query that feels like it is taking more time then it should be. This only applies on the first query for a given set of parameters, so when cached there is no issue.
I am not sure what to ...
1
vote
0answers
31 views
Why is my SQL statement so slow executed on Postgre?
I have a table with 1.5 million rows, now I need to do a data migration on the table: First add a column on the table, then update the column to be the same as another column in the same table:
...
1
vote
0answers
52 views
Optimizing query and join in PostGIS View
I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB.
I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
1
vote
1answer
56 views
Optimising Hash And Hash Joins in SQL Query
I have a bunch of tables in PostgreSQL and I run a query as follows:
SELECT DISTINCT ON ...some stuff...
FROM "rent_flats"
INNER JOIN "rent_flats_linked_users"
ON ...
0
votes
0answers
55 views
what's the cause of this very slow but simple query?
I'm baffled by this,
EXPLAIN ANALYZE SELECT DISTINCT
"spree_variants"."product_id"
FROM
"spree_variants"
INNER JOIN "spree_prices" ON "spree_prices"."variant_id" = "spree_variants"."id"
I ...
0
votes
1answer
112 views
ORDER BY column from right table of LEFT OUTER JOIN
I'm having serious performance issues when using a LEFT OUTER JOIN and trying to use a column in the right table in Postgres. I have a table of users and a table with online_users that lists user ids ...
