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.
0
votes
1answer
20 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
54 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 ...
2
votes
0answers
64 views
Improve performance for order by with columns from many tables [migrated]
Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
1
vote
0answers
28 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:
...
-2
votes
0answers
21 views
PGSQL: I want to optimize the query to take less then 1 sec. for returning 5012 records
--EXPLAIN ANALYZE
WITH
gl_header_records AS (
SELECT
DISTINCT ( gd.gl_header_id )
FROM
gl_details gd
JOIN
gl_headers gh
ON ( gd.cid = gh.cid
AND gd.gl_header_id = gh.id
...
1
vote
0answers
38 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
48 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
50 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
47 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
48 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
46 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
36 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
92 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
49 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
114 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
101 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
48 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
69 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
37 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
38 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
58 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
26 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
40 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
63 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
312 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
57 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
227 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
153 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,
...
7
votes
1answer
93 views
Postgresql function executed much longer than the same query
I'm using PostgreSQL 9.2.9 and have the following problem.
There are function:
CREATE OR REPLACE FUNCTION report_children_without_place(text, date, date, integer)
RETURNS TABLE (department_name ...
0
votes
1answer
27 views
optimize query using explain analyze result
following is a part of my union all
SELECT tbl_12.AcGrCode
,tbl_12.AcId
,tbl_12.AcName
,tbl_11.AcgrName
,tbl_16.VrDate
,tbl_16.PDC
...
2
votes
0answers
55 views
Postgres query on multi column unique index takes longer than expected
I have a large table with 2 columns:
name::text
url::text
I have indices on both columns and I have another unique index on (name, url). When I execute
select count(*) from t where name = 'foo'
...
0
votes
2answers
53 views
Query with many self-joins is slow
I have a query that involves doing tons of joins to the same table that stores lead data. In campaigns that I have tons of leads (5k) the queries runs very slow. FYI there are 40657335 records in the ...
1
vote
3answers
81 views
Query performance with concatenation and LIKE
Can someone explain the performance difference between these 3 queries?
concat() function:
explain analyze
select * from person
where (concat(last_name, ' ', first_name, ' ', middle_name) like ...
5
votes
1answer
161 views
How to optimize postgresql query with joins?
I wrote the query below this cost is around 1770077 I want to reduce the cost please suggest the best option.
SELECT eco.operationalstatus,SI.storecode,count(SI.gustoreid), ...
0
votes
4answers
51 views
Counting preceding occurences of an event within a given interval for each event row with a window function
I have table storing events occurring to users as shown in http://sqlfiddle.com/#!15/2b559/2/0
event_id(integer)
user_id(integer)
event_type(integer)
timestamp(timestamp)
A sample of the data ...
1
vote
0answers
63 views
make postgres use more memory
We have a dedicated DB server running Ubuntu 14.04 with 8 cores, 16GB of Memory and and 3TB HD.
Postgres is version 9.4.
I have one batch job running that crawls through a lot of data (combining ...
0
votes
0answers
50 views
how to make my pg query faster
Im new to pg, and have a table like this one:
CREATE TABLE tbl_article (
uid TEXT PRIMARY KEY,
...
tags JSONB
)
CREATE INDEX idxgin ON tbl_article USING gin (tags);
uid ...
2
votes
2answers
25 views
Compound index with three keys, what happens if I query skipping the middle one?
With PostgreSQL, I want to use a compound index on three columns A, B, C. B is the created_at datetime, and occasionally I might query without B.
What happens if I compound index on (A, B, C) but ...
1
vote
1answer
34 views
How to optimally query many rows by id?
In my project, I often need to query many rows by their ids. I do queries like:
select * from mytable where id in (...)
The problem is, these queries are too slow for me. My table has around 3M ...
0
votes
1answer
67 views
Is there any way to speed up this Postgres bitmap heap scan?
Database newbie here. This is my query, I'm using Postgres 9.3.5:
=# explain analyse SELECT SUM(actual_cost) as cost, SUM(total_items)
as num_items, processing_date FROM frontend_items
WHERE ...
2
votes
2answers
104 views
Bitmap scan in Postgres 8.3 2x faster than index scan Postgres 9.4?
Upgrading Postgres from 8.3.8 to 9.4.1 on new hardware. A representative set of queries shows that new system's performance ranges from 1x to 3x faster. However, one of our high-load areas is always ...
-1
votes
2answers
75 views
Why postgres function so slow but single query is fast?
I have function to get employee in 'Create' status.
CREATE OR REPLACE FUNCTION get_probation_contract(AccountOrEmpcode TEXT, FromDate DATE,
...
1
vote
1answer
47 views
Is it possible to answer queries on a view before fully materializing the view?
In short: Distinct,Min,Max on the Left hand side of a Left Join, should be answerable without doing the join.
I’m using a SQL array type (on Postgres 9.3) to condense several rows of data in to a ...
0
votes
0answers
155 views
postgres: join against partitioned table
I want to join against a huge partitioned table. The planner probably assumes that the partitioned table is very cheap to scan.
I have the following query:
select *
from (
select users ...
0
votes
1answer
89 views
Slow Postgres performance with in clause
I have a quiet simple query:
SELECT
contract.ctrId,
contract.ctrNr
FROM
changeLog,
contract
where
changelog.entid in (select contract.ctrid from contract where contract.ctrnr LIKE ...
0
votes
2answers
64 views
Postgres using an index for one table but not another
I have three tables in my app, call them tableA, tableB, and tableC. tableA has fields for tableB_id and tableC_id, with indexes on both. tableB has a field foo with an index, and tableC has a field ...
4
votes
2answers
386 views
SQL function very slow compared to query without function wrapper
I have this PostgreSQL 9.4 query that runs very fast (~12ms):
SELECT
auth_web_events.id,
auth_web_events.time_stamp,
auth_web_events.description,
auth_web_events.origin,
...
2
votes
1answer
57 views
FULL JOIN with =any doesn't use indexes
Using Postgres 9.3.5, I can't seem to get a full outer join with an =any where-clause to use the relevant indexes.
A minimal example:
create table t1(i int primary key, j int);
create table t2(i int ...
2
votes
2answers
295 views
PostgreSQL - Slow query joining on a VIEW
I'm trying to do a simple join between a table (players) and view (player_main_colors):
SELECT P.*, C.main_color FROM players P
OUTER LEFT JOIN player_main_colors C USING (player_id)
WHERE ...
1
vote
2answers
54 views
Looking in array with a big set of input values
I have a table that looks like (with an example of number of rows in each to get the kind of ration):
expectedreportsnodes (1 000 000 rows):
nodejoinkey | integer | not null
nodeid | ...
