PostgreSQL

01 November 2025 14:46

/switch_model_chatgpt5

PostgreSQL

01 November 2025 14:43

Sorry, but I’m unable to assist because your question is unclear. It’s difficult to understand exactly what you’re trying to achieve with your transaction and reporting tables. Please explain your setup again using clear examples, such as table names, key fields, and what kind of reports or operations you want to perform. This will help me provide a useful answer.

PostgreSQL

01 November 2025 14:39

I’m sorry, but as a large language model, I’m unable to assist with that request right now. I hope you find the right solution for your needs. If you have other questions, please let me know.

PostgreSQL

01 November 2025 14:38

I do not understand what you are looking for

PostgreSQL

01 November 2025 14:33

Hello friends, I want to do something. I want to do something. When I have accounting in my service, I have one that is used to store payments, wallets, transactions, and transactions. Because I am in the middle of my transactions, I came to my transaction table. I used UID. Every time I want to select a report, I find it for each user. It is a bit difficult for me to find it with a specific user ID. Do you think it is wrong? When I create transactions, I create a separate table for transaction reports. Then I show the reports that the user needs to see in a more limited way. With the primer, when it is added, and I put these two queries in one transaction at the same time. Do you think this is wrong? I separate the report altogether, for example, a more important table from transactions. This will not make the database heavy or, for example, an additional table, because it is easier this way.

PostgreSQL

01 November 2025 11:43

One of my favourite things to do is

SELECT id, name FROM table_a WHERE NOT EXISTS (SELECT 1 FROM table_b WHERE some_condition)

PostgreSQL

01 November 2025 11:39

Is it?🤔 i thought that was very fast

PostgreSQL

01 November 2025 11:23

I usually try to swap it for a more explicit join, especially when working with many many values

PostgreSQL

01 November 2025 11:20

The query itself is fine. It's straightforward, idiomatic and reflects the db structure.

Optimizer failed a bit, especially with condition result estimation. It could be corrected by different methods: changing (increasing samples and adding some complex sampling) statistics, manipulating planner cost settings, adding indexes....

However, the database structure failed miserably. I see no reason to do something before getting it to a sane state.

PostgreSQL

01 November 2025 11:03

Try reducing random_page_cost (the default values are still tailored for spinning rust)

PostgreSQL

01 November 2025 10:36

Query is not well .... What?

PostgreSQL

01 November 2025 02:09

Shouldn't.

Well, 10k nested loops should be comparable to seq scan actually, even somewhat worse may be. Or not worse — considering nearly all segment access in a nested loop would result in just a few comparisions on the root index page.

However, that's just 16 nested loops necessary — and it should be much faster than seq scans needed for hash join.

So the planner estimation fail does contribute a lot to the slowness.

PostgreSQL

31 October 2025 22:41

@tzirechnoy , If that table has been partitioned by ID only as Anba mentioned, so forcing use of index would make things even worst, isn't it?

PostgreSQL

31 October 2025 20:18

2. Extra table partitioning is also useless and damaging, but not that much.

The planner, however, missed badly on your filter — which is not good. Thing may be fixed a bit by some analyze factors changes or index changes.

Consider adding deleted_at and date to that index at first. Maybe partial index on deleted_at is null would also be a good idea.

PostgreSQL

31 October 2025 18:28

Not a good approach, I have to mention. Refactoring seams to be the best action to take this far.

PostgreSQL

01 November 2025 14:46

Can i send to your pv ?

PostgreSQL

01 November 2025 14:40

Look, there's a problem. I want to store two data and two different tables at the same time. While the tables may be duplicates, their data, but I want one for reporting. It has fewer attributes than, for example, that one and the other, for example, the main bank transaction. For example, I choose one separately for reporting transactions and one separately for that transaction process in the database because that transaction process in the database has a uid and every time I want to access it and show it to the user, this is problematic and it becomes very slow. The solution I used is good now.

PostgreSQL

01 November 2025 14:38

He thinks you are a LLM

PostgreSQL

01 November 2025 14:37

@unfoxo

PostgreSQL

01 November 2025 11:44

Ah yes! I do that a lot too

PostgreSQL

01 November 2025 11:39

Maybe it’s time to visit my (NOT) EXISTS lessons

PostgreSQL

01 November 2025 11:33

Well... Not really. It denys some fast direct algorithms for some indexes — but that part is fast anyway, there is no urge to fix it in this query.

Splitting may also switch to a nested loop, yes, but... Not because in/any is slow — it's because another set of problems.

PostgreSQL

01 November 2025 11:22

Isn't "in/any" the source of all evils?

PostgreSQL

01 November 2025 11:08

Hi @NickBluth , apologies . Can you bit more explain how this change could avoid 10k loops and target only 16 necessary loops

PostgreSQL

01 November 2025 10:58

Is there a right approach which could be followed to have just necessary 16 nested loops?

PostgreSQL

01 November 2025 06:01

It means query is not well framed?

PostgreSQL

31 October 2025 22:42

I got your point now. Yes, I totally agree with you.

PostgreSQL

31 October 2025 20:20

You may try to explicitly disable seq scans to provoke PK scan on screenshots....
But, really, getting rid of partitions should go first.

PostgreSQL

31 October 2025 20:07

0. You partition of screenshots is a useless crap.

Just stop it. It's far beyound insanity.
(Most actual partition schemes of relational databases are crap, in fact — people naturally tend to think that working with smaller chunks of data would be easier to DBMS. No, relational databases are written to work with relatively large relations, partitions usually make the database to work worse, slower and to be harder to manage properly.

Your thousand partitions below 10k narrow values is something exceptionally bad even comparing to indusrry average, however.)

1. Show \d+ of every table somewhere. Even with that partitioning — this should fall to nested loop on pk, that would be not fast — but generally acceptable.

PostgreSQL

31 October 2025 18:24

Oh also, check this out

https://postgresqlco.nf/doc/en/param/enable_partitionwise_join/