Tagged Questions

Questions about PostgreSQL query optimisation. In your question please include the full original text of SQL queries, `EXPLAIN(BUFFERS, ANALYZE)` for those queries, and `CREATE TABLE` and `CREATE INDEX` statements or `psql` `\dt+ tablename` output. If you're looking to tune without modifying ...

learn more… | top users | synonyms

1
vote
3answers
41 views

Why does the following join increase the query time significantly?

I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can't really explain the following: EXPLAIN ANALYZE SELECT COUNT(impression_id), ...
6
votes
1answer
55 views

Indexed ORDER BY with LIMIT 1

I'm trying to fetch most recent row in a table. I have a simple timestamp created_at which is indexed. When I query ORDER BY created_at DESC LIMIT 1, it takes far more than I think it should (about ...
0
votes
0answers
32 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 ...
0
votes
1answer
45 views

Heroku Postgres query speed sanity check. Am I doing it wrong?

I'm running a rails website on Heroku and am doing some performance profiling and improvements. I was wondering if anyone with experience profiling Postgres performance on Heroku could tell me if I'm ...
0
votes
0answers
66 views

Optimizing simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
0
votes
0answers
46 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 ...
2
votes
1answer
89 views

Slow postgres query with multiple joins to same table

The following query is running very slowly for me: SELECT r.comp,b.comp,n.comp FROM paths AS p INNER JOIN comps AS r ON (p.root=r.id) INNER JOIN comps AS b ON (p.base=b.id) INNER JOIN comps ...
1
vote
4answers
70 views

Postgres NOT IN performance

Hi any ideas how to speed up this query? Input EXPLAIN SELECT entityid FROM entity e LEFT JOIN level1entity l1 ON l.level1id = e.level1_level1id LEFT JOIN level2entity l2 ON l2.level2id = ...
1
vote
1answer
53 views

PostgreSQL Performance: keep seldomly used small database in memory while server busy with big database

I have a server with 64GB RAM and PostgreSQL 9.2. On it is one small database "A" with only 4GB which is only queried once an hour or so and one big database "B" with about 60GB which gets queried ...
0
votes
1answer
51 views

Why does Heroku postgres take longer than local mysql?

In my local development a query I make using ActiveRecord takes 1695ms including rendering the view, however on Heroku I get a request timeout with the same query, which means it takes more than 30s. ...
0
votes
1answer
52 views

Postgresql Explain analyse : time between nodes

in a PostgreSQL explain plan like this one : http://explain.depesz.com/s/wwO What can justifie the time between the last hashjoin and hashaggregate ? Only the volume of data to manipulate ?
0
votes
0answers
32 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 ...
1
vote
1answer
55 views

Postgreslq Explain analyse : hidden time

I've a long query and explain analyze isn't helping. Version of PostgreSQL : 9.1 The current plan: http://explain.depesz.com/s/sWG The query : SELECT temps_mois.rfoperyea AS c0, ...
0
votes
0answers
54 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
vote
0answers
71 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 ...

1 2 3 4 5
15 30 50 per page