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
114 views
Table Partitioning vs non-Partitioned Table with many indexes
I have a DB "DB_One" with a Master Table called t_d_gate_out with 8 indexes on it. I created another DB with partitioned t_d_gate_out (let's call it "DB_Two"). It was partitioned by month and year ...
0
votes
2answers
107 views
SELECT on 80 Million rows taking too long
Table:
Table "public.hugetable"
Column | Type | Modifiers | Storage | Description
---------+-----------------------+-----------+----------+-------------
...
-1
votes
2answers
453 views
Left outer join performace issues in postgresql
How to improve the performance of query given below
select
distinct o1.id as c1,
a1.id as c2,
o1.t1_id as c3,
o1.t2_id as c4,
o1.t_p_id ...
5
votes
1answer
150 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), ...
1
vote
1answer
28 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 ...
1
vote
1answer
248 views
Poor Performance of Entity Framework over simple queries using Postgresql
In my new project I am keen to use .NET Entity Framework on Postgresql. So I first compared the performance of Entity Framework with simple SQL query. For this I developed a sample desktop application ...
0
votes
1answer
22 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
46 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
1answer
214 views
PostgreSql Query, Performance NOT IN, LEFT OUTER JOIN
I have Table employee, Table sport that have n to m relations, that's means, i also have one more table, let's say table emp_sport
table employee
emp_id emp_name
------------------
e1 name1
e2 ...
0
votes
1answer
210 views
Multiple connections to PostgreSQL with huge number of INSERTs
This question is involved by this one: How to speed up insertion performance in postgresql
So, I have java application which is doing a lot of (aprox. billion) INSERTs into PostgreSQL database. It ...
0
votes
1answer
254 views
PostgreSQL large single transaction slower than multiple transaction
On Postgres, I recently saw a case where there was a huge amount of selects being handled in a single transaction. This was really slow, so the solution was to break the selects into multiple ...
0
votes
1answer
132 views
Postgresql FTS solution for existing data
In rails app, I am trying and tinkering to add fts in postgres for existing data.
Here is what I have done:
class AddNameFtsIndexToCompanies < ActiveRecord::Migration
def up
...
0
votes
1answer
87 views
Postgresql Update command is too slow
I have the following postgresql table:
CREATE TABLE "initialTable" (
"paramIDFKey" integer,
"featAIDFKey" integer,
"featBIDFKey" integer,
"featAPresent" boolean,
"featBPresent" ...
-1
votes
1answer
43 views
How to indexing lat lon on PostgreSql to make it faster
I am using PostgreSQL 8.4 and have table with lat lon. This 2 field(lat,lon) are character varying and total record inside are more than 100K.
I already create index like this below:-
CREATE INDEX ...
2
votes
0answers
50 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'
...
2
votes
0answers
110 views
Speed up insert new data to PostgreSQL
My current workflow is as follows.
However, it is extremely slow. It only can handle millions of data per/day
I want to speed it up. Any idea?
query_expression (generated by Ruby rake file, then ...
1
vote
0answers
55 views
Why does 'WHERE col = int' make this PostgreSQL query slow?
I'm doing a full-text PostgreSQL search, and I've noticed that simply changing the operator on one of my filter conditions from '<>' to '=' substantially increases the query time, from 24.7 ms to ...
1
vote
0answers
112 views
Hstore vs many-to-many relationship search performance
Completely hypothetical question to compare performance of hstore is postgress
Let's say each user has a list of followers. There are 2 way to implement it
Many-to-Many relationship with a ...
0
votes
0answers
24 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
47 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 ...
0
votes
0answers
100 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
0answers
109 views
Slow SQL execution when querying from view
I execute select * from a view on 3 tables (gis_wcdma_cells, wcdma_cells, wcdma_cells_statistic):
CREATE OR REPLACE VIEW gisview_wcdma_cell_statistic AS
SELECT gis_wcdma_cells.wcdma_cell_id,
...
0
votes
0answers
41 views
Performance issue - Process postgres database
Good morning!
I have a web application connected to a postgres database in Ubuntu.
I have the problem that there is a process within my database which consumes 100% of the CPU time.
How can I ...
0
votes
0answers
351 views
postgresql create index on join table/nested query to make it fast
postgresql(version 1.18.1)
create index on join table/ or the way below is nested query
i have two table here:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL ...
0
votes
0answers
49 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
45 views
Can not write to PPAS logfile
Now, I am testing query from application to PPAS via PgPool.
When I query
select a.name, b.name from tb_01 a, tb_02 c where a.id = b.id_ref
... or update/insert, I have to wait for a long time ...