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.
3
votes
2answers
24 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
45 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
18 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
25 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
24 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
28 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
45 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
87 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
77 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
61 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
23 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
87 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
28 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
49 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
59 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
202 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
33 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
48 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
46 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
47 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
70 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
48 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
90 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
45 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
53 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
102 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
29 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
46 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
51 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
53 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
69 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 ...
1
vote
2answers
76 views
How does Postgres perfom ORDER BY if a b-tree index is built on that field?
I have a table bsort:
CREATE TABLE bsort(a int, data text);
Here data may be incomplete. In other words, some tuples may not have data value.
And then I build a b-tree index on the table:
CREATE ...
1
vote
1answer
51 views
Tuning a query in PostgreSQL
What is the best way to tune the below SQL for PostgreSQL which seems to be very costly? Will creating a temporary table gives optimal cost?
UPDATE table1
SET id = qry.crmId
FROM (
SELECT ...
5
votes
1answer
61 views
Storing 'Rank' for Contests in Postgres
I'm trying to determine if there a "low cost" optimization for the following query. We've implemented a system whereby 'tickets' earn 'points' and thus can be ranked. In order to support analytical ...
3
votes
2answers
112 views
How to increase query efficiency for a large volume of data in a PostgreSQL database?
I have a PostgreSQL database with 1.2 billions rows, attempted to make an application that queries rows a million at a time, with an option to query larger intervals.
At first I was just querying a ...
1
vote
2answers
50 views
How to store and query version of same document in PostgreSQL?
I am storing versions of a document in PostgreSQL 9.4. Every time the user creates a new version, it inserts a row so that I can track all changes over time. Each row shares a reference_id column with ...
2
votes
0answers
579 views
JSONB PostgreSQL data type with JAVA - inserts and joins
Trying my hand at JSONB datatype first time(discussion continued from (Join tables using a value inside a JSONB column) on advise from @Erwin , starting new thread)
Two tables (obfuscated data and ...
3
votes
4answers
138 views
Spatial query on large table with multiple self joins performing slow
I am working on queries on a large table in Postgres 9.3.9. It is a spatial dataset and it is spatially indexed. Say, I have need to find 3 types of objects: A, B and C. The criteria is that B and C ...
0
votes
1answer
67 views
Table Locking in PostgreSQL
I have a PL/pgSQL function which takes data from a staging table to our target table. The process executes every night. Sometimes due to server restart or some maintenance issues we get the process ...
0
votes
2answers
80 views
How to speed up the query in PostgreSQL
I have DB in PostgreSQL with a big data (now it is somewhere around 46 GB and the db will keep growing). I created indexes on often used columns and adapted the config file:
shared_buffers = 1GB
...
0
votes
0answers
38 views
PostgreSQL Performance Worsening
I'm running a PostgreSQL DB on a windows VM. Every morning I run some large customer matching algorithms which run in 10-15 minutes. However, recently, the machine has begun to take longer and longer ...
1
vote
1answer
54 views
GIN index not used for small table when 0 rows returned
In a Postgres 9.4 database, I created a GIN trigram index on a table called 'persons' that contains 1514 rows like the following:
CREATE INDEX persons_index_name_1 ON persons
USING gin (lower(name) ...
3
votes
2answers
87 views
PostgreSQL not using index on a filtered multiple sort query
I have a pretty simple table
CREATE TABLE approved_posts (
project_id INTEGER,
feed_id INTEGER,
post_id INTEGER,
approved_time TIMESTAMP NOT NULL,
post_time TIMESTAMP NOT NULL,
PRIMARY ...
0
votes
1answer
28 views
Configuring Merge Join in PostgreSQL
I'm using PostgreSQL with big tables, and query takes too much time.
I have two tables. The first one has about 6 million rows (data table), and the second one has about 30000 rows (users table).
...
0
votes
1answer
80 views
Does PostgreSQL self join ignore indexes?
I have the following table in Postgresql 8.4.12:
Table "public.ratings"
Column | Type | Modifiers
--------+------------------------+-----------
userid | character ...
1
vote
1answer
105 views
Partition pruning based on check constraint not working as expected
Why is the table "events_201504" included in the query plan below? Based on my query and the check constraint on that table I would expect the query planner to be able to prune it entirely:
...
6
votes
2answers
614 views
Execute multiple functions together without losing performance
I have this process that has to make a series of queries, using pl/pgsql:
--process:
SELECT function1();
SELECT function2();
SELECT function3();
SELECT function4();
To be able to execute everything ...
1
vote
1answer
102 views
most impactful Postgres settings to tweak when host has lots of free RAM
My employer runs Postgres on a decently "large" VM. It is currently configured with 24 cores and 128 GB physical RAM.
Our monitoring solution indicates that the Postgres processes never consume more ...
9
votes
4answers
271 views
Fastest way to PostgreSQL Distinct and Format
I have a 3.5 million rows in table acs_objects and I need to retrieve column creation_date with year only format and distinct.
My first attempt : 180~200 Sec (15 Rows Fetched)
SELECT DISTINCT ...
2
votes
1answer
253 views
Improve performance of PostgreSQL array queries
I am storing large vectors (1.4 million values) of doubles in a PostgreSQL table. This table's create statement follows.
CREATE TABLE analysis.expression
(
celfile_name character varying NOT NULL,
...
