Linked Questions
23 questions linked to/from Are regular VACUUM ANALYZE still recommended under 9.1?
17
votes
2
answers
12k
views
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
- 55.8k
12
votes
3
answers
7k
views
PostgreSQL Initial Database Size
There are 2 parts to my question.
Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?
I'...
- 733
6
votes
1
answer
22k
views
Efficient way to insert/update/delete table records from complex query in Postgres 9.x
I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and re-...
- 305
9
votes
1
answer
18k
views
Index usage on a temporary table
I have two rather simple queries. The first query
UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;
and it's plan
duration: 0.125 ms plan:
Query Text: UPDATE ...
- 203
5
votes
2
answers
4k
views
Postgres Slow Queries - Autovacuum frequency
We've noticed the performance of our platform drop in recent weeks so I've run the following:
select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
from pg_stat_user_tables
...
- 237
8
votes
2
answers
8k
views
how do you prevent dead rows from hanging around in postgresql?
I have production and staging RDS instances on amazon, and staging's data is a direct copy of production so both instances have duplicate data.
Doing a EXPLAIN ANALYZE SELECT * from my_table WHERE ...
- 233
6
votes
2
answers
8k
views
What is the most efficient way to count the number of rows in a table?
I am using Postgres with the following query:
select count(*) from image;
The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
- 581
9
votes
2
answers
7k
views
Optimizing queries on a range of timestamps (one column)
I am using Postgres 9.3 through Heroku.
I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
- 1,033
10
votes
1
answer
4k
views
Debug query on big table that is sometimes slow
I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
- 203
5
votes
2
answers
3k
views
Performance hickups after Postgres 9.4 upgrade
After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into 9....
- 3,335
3
votes
2
answers
5k
views
Speed difference between Drop table and Truncate table in Postgres
I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm ...
- 131
7
votes
1
answer
1k
views
PostgreSQL partial index unused when created on a table with existing data
In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
- 587
3
votes
1
answer
1k
views
Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?
Following the migration of an application and its database from a classical PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using 9.6 version), we have found that a specific ...
- 133
6
votes
1
answer
981
views
Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?
I use PostgreSQL 9.6.
I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't ...
- 145
5
votes
1
answer
2k
views
PostgreSQL maintenance
I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III.
I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. ...
- 151