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 ...
- 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.
- 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 ...
- 152k
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 ...
- 152k
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 ...
- 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 ...
- 152k
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)....
- 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 ...
- 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 ...
- 33.2k
Only top scored, non community-wiki answers of a minimum length are eligible