3 votes
Accepted

Recommended way to index a timestamp column in postgres?

You won't be able to create an index for that query, and indeed the query is not very reliable, because it depends on the current setting of the parameter timezone. You shouldn't be using AST, but ...
user avatar
  • 33.2k
3 votes
Accepted

Is there a performance penalty on using databases instead of schemas in a single PostgreSQL cluster?

If you don't need to join data from several databases, splitting the data into multiple databases is the cleaner solution, and there should be no performance disadvantages.
user avatar
  • 33.2k
3 votes

Best performance to get distinct values for a given key from a big table

Assuming many entries for the same (key, value), an emulated index skip scan should be your best option. WITH RECURSIVE cte AS ( ( -- parentheses required SELECT value FROM attr WHERE ...
user avatar
2 votes

Improving postgreSQL 13.3 performance on a materialised view?

Your query isn't that slow. But after cleaning up, it should be a bit faster, yet: WITH cte AS ( -- apply common basic filters *once* SELECT is_incoming, express_train, train_id FROM ...
user avatar
2 votes

Why does Postgres not use an index when joining a materialized view?

The horrible estimation of 'transfers' condition (expected 2600, found 1) caused the cost of the hash join and the nested loop to appear falsely similar. Why does misestimating by 2600 fold cause one ...
user avatar
  • 32k
2 votes
Accepted

UPDATE FROM with a large table is slow and uses Seq Scans

... we need to set the 'current' flag true for 98% of rows So NOT current will be the rare case. Seems like you have been trying to do things back to front so far. Your current indexes are actively ...
user avatar
1 vote

UPDATE FROM with a large table is slow and uses Seq Scans

I don't think you should try to avoid every seq scan. But you do not want a seq scan which will be executed a large number of times (like in a un-hashed subplan, or in the 2nd child of a nested loop)....
user avatar
  • 32k
1 vote

Is there a performance penalty on using databases instead of schemas in a single PostgreSQL cluster?

Yes. The main problem is that you can't reuse connections. Which means that you will have more connection, which means that you will use more memory. OTOH separate DBs means that each DB stats will be ...
user avatar
  • 111
1 vote

Postgres - how to manage intensively growing DB

Partition your tables by range. Create an additional tablespace on the slow, cheap storage. Move old partitions to the slow tablespace (and drop partitions with very old data). You should have ...
user avatar
  • 33.2k

Only top scored, non community-wiki answers of a minimum length are eligible