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.

learn more… | top users | synonyms

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 ...