New answers tagged

0 votes

Why is restored database faster than original?

You added insightful findings about dropped columns later. That might explain some of the performance difference. But normally, 99 % of that would be covered by VAUUM FULL, without having to create a ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Why Isn't Postgres Using My Functional Index?

I think this is a statistics/estimation issue. I initially thought it might also be a datatype casting issue, since the column is character varying(40) and the index is using text but now realise ...
michristofides's user avatar
0 votes

Strategy for Postgres updates with very high write load, but low consistency requirements

let's say we have 100 users querying the database per second on average, and each one fetches a result set consisting of 200 thumbnails. Then the total writes per second needs to scale to 100 * 200 / ...
bobflux's user avatar
  • 1,391
0 votes
Accepted

Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool

After looking in to the issue with parameters more, I found the following stackoverflow question suggesting that: After running the query a few times, it decides that the generic plan is just as good ...
Arnfred's user avatar
  • 101
6 votes
Accepted

What are large files in Postgresql base directory

Those are the files behind temporary tables. Looks like somebody is creating large temporary tables. Such tables get deleted when the database session ends. The files get cleaned up automatically when ...
Laurenz Albe's user avatar
  • 50.2k
0 votes
Accepted

Terrible performance joining a view to a fast subquery / CTE

We ended up converting these views into a series of materialized CTEs, and inverting some of the dependencies between them to reduce duplication. Originally the base DB views were intended to be ...
Brian Moeskau's user avatar
1 vote
Accepted

Cheaper query plan takes much longer to execute

It switched to a sequential scan instead of index scan where there's more memory It switched from GroupAggregate to HashAggregate. The seq scan is an incidental consequence to this change. ...
jjanes's user avatar
  • 39.4k
1 vote

Cheaper query plan takes much longer to execute

You could try to use extended statistics to improve the estimate: CREATE STATISTICS mystats (ndistinct) ON col1, col2 FROM table1; ANALYZE table1; I am not sure if that is enough to make the fast ...
Laurenz Albe's user avatar
  • 50.2k
-1 votes

Get the best performance out of postgres

The problem needs to be analyzed in detail, perhaps you should see if the SQL really takes up so much CPU (use explain (analyse,verbose,buffers) your sql, or is it affected by other sql)?
sheng yan's user avatar
1 vote

Why doesn't Postgres apply limit on groups when retrieving N results per group?

The problem is that scanning that index would not return the rows in created_at order, so all matching rows would have to be fetched. The optimizer doesn't think that such an index scan would be ...
Laurenz Albe's user avatar
  • 50.2k
2 votes
Accepted

Postgres is not using CPU cores unsless running from psql or using "explain analyze"

You don't tell us many relevant details, but I guess that the “SQL client” is DBeaver or something else that uses the JDBC driver and limits the number of result rows with java.sql.Statement....
Laurenz Albe's user avatar
  • 50.2k
1 vote
Accepted

Scalability of Postgres for table with large number of indexed columns

Since it sounds like your main curiosity is around tradeoffs of consistency for improved concurrency, the topic you're probably looking to learn about is called Transaction Isolation Levels. This is ...
J.D.'s user avatar
  • 37.1k
1 vote

Scalability of Postgres for table with large number of indexed columns

the app details truly don't matter to the question If you're asking a question, it means you don't know the answer. In this case it is a bit presumptuous to hide information because you feel it's not ...
bobflux's user avatar
  • 1,391
1 vote

Scalability of Postgres for table with large number of indexed columns

Being single-threaded doesn't matter. It is multi-process with shared memory, and the ways that processes manage concurrency is not meaningfully different than how threads do. There are two kinds of ...
jjanes's user avatar
  • 39.4k
2 votes
Accepted

Why is DISCARD ALL called many times in Azure PostgreSQL?

DISCARD resets your database connection to "factory default". I guess you're using some form of connection pooling. That's a good thing because opening a connection consumes a significant ...
bobflux's user avatar
  • 1,391
1 vote

Scalability of Postgres for table with large number of indexed columns

An individual PostgreSQL database session used to be single-threaded, in that there is a single backend process that processes the SQL statements for the connection. PostgreSQL 9.6 introduced parallel ...
Laurenz Albe's user avatar
  • 50.2k
2 votes
Accepted

Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause

Yes, the optimizer rearranges the WHERE conditions as it thinks fit, and all your examples will result in the same execution plan.
Laurenz Albe's user avatar
  • 50.2k
0 votes

PostgreSQL - summing arrays by index

Another way of doing it is by using a plpgsql function: create or replace function sum_int_arrays(a int[], b int[]) returns int[] as $$ declare c int[]; begin for i in 1..array_length(a, 1) loop ...
Ludovic Kuty's user avatar
1 vote
Accepted

Does postgres optimizes repeated expression in select query?

As far as I know, PostgreSQL has no such optimization and will calculate the expression multiple times. If the query is expensive enough and you have JIT enabled, it may create custom executable code ...
Laurenz Albe's user avatar
  • 50.2k

Top 50 recent answers are included