2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
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.
Try reducing random_page_cost (the default values are still tailored for spinning rust)
Читать полностью…
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.
@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?
Читать полностью…
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.
Not a good approach, I have to mention. Refactoring seams to be the best action to take this far.
Читать полностью…
If they are equal, the planner will match partition-by-partition, and with indexes this will generally be very fast. But imagine the following tables partitioned with the following ranges:
t1 t2
0 0
100
200
250
300
400
500 500
(p(t1)+p(t2))! joins*
You mean range partitioning should be done exactly in the same way for both the tables ?
Читать полностью…
You have a partitioned table, and the partition key is not being used in your filter. That's the reason why you have so many Seq Scans in the lower level. Those scans represent 34,342.741 of the total cost of 37,771.858. You may try some approaches to optimize it.
The client application may run your query in a Loop, including the partition key in the filter on each iteration. The results shall be accumulated in a result list, on client side.
@unfoxo , plan for the impacted query is here
https://explain.depesz.com/s/mUPT (It's typically during the time when query gets suceeded within expected latency)
Oh damn. My bad. I'm not using any AWS services. :D
Читать полностью…
before we go too far. what kind of storage does your on prem use ? ssd, nfs, raid ?
Читать полностью…
But no other go except to run with that. Not too many users. So not worthy for even more additional compute
Читать полностью…
Hi @NickBluth , apologies . Can you bit more explain how this change could avoid 10k loops and target only 16 necessary loops
Читать полностью…
Is there a right approach which could be followed to have just necessary 16 nested loops?
Читать полностью…
I got your point now. Yes, I totally agree with you.
Читать полностью…
You may try to explicitly disable seq scans to provoke PK scan on screenshots....
But, really, getting rid of partitions should go first.
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.
Oh also, check this out
https://postgresqlco.nf/doc/en/param/enable_partitionwise_join/
Yeah that's the reason of having 1900+ parallel seq scans on to the screenshot partition. That table has been range partitioned with id column only
Читать полностью…
"screenshot_capture"."screenshot_id" = "screenshot"."id"
Make sure both screenshot_capture and screenshot are partitioned in the same exact way (same ids), even having slightly different ranges will prevent postgresql from optimizing that search
Have you got index in every ID column( extra.id, screenshot.id.. ? And an index with ID and partition key in every partition table ?
Читать полностью…
Noone knows yet.
Performance tuning is about thorough checking execution plans, timings, perf sometimes — generally, about problem isolation, algorithmical optimisation and numerical stress test results.
There is no room for random parameter guessing by strangers here.
I can talk only based on my own experience. Setting max_parallel_workers to equal or higher than CPU cores will allow PostgreSQL to use all available CPU when needed. A good practice may be you leave some resources to the OS, or else oom killer may be triggered and shut down your cluster.
Читать полностью…
Anyway, try to drop your caches, then run the query cold and warm (aka fast and slow) using EXPLAIN ANALYZE with BUFFERS, and share both plans on https://explain.dalibo.com so we can look further into what's slowing down between the runs
Читать полностью…
I assume RDS runs bare metal or containerized, while you have the extra performance drop of virtualization
Читать полностью…