New answers tagged statistics
0
As you're using hibernate this is going to be a bit cumbersome. You can 'guide' the optimizer by telling him how to do the joins.
For example select * from a inner hash join b on a.x = b.x will tell the optimizer to do a hash join. It could also be a merge join or a loop hint.
This might result into writing the sql queries manually. Using hints should also ...
8
As the tooltip shows, 7643 8KB pages are spilled = 59.71MB. The rest of the sort occurs within the allocated memory.
The sort spills for two reasons:
The sort is parallel. The accurate memory estimate is split evenly among the sort threads, and cannot be dynamically reallocated between threads at runtime. If the rows are not distributed evenly, one or more ...
4
Question: How much data is spilled into disk? 400 MB or 60 MB(7643KB*8)?
60MB. You can verify this by dumping
select sum(num_of_bytes_written) / 1024 / 8 approx_pages_written
from sys.dm_io_virtual_file_stats(db_id('TempDb'),null)
before and after the query
If everything is okay with estimates, stats are up to date, box has sufficient memory, and no ...
answered Jun 30 at 20:09
David Browne - Microsoft
24.8k2 gold badges25 silver badges59 bronze badges
1
Until our maintenance job will update statistics again with fullscan
next time, the auto statistics update runs and resets the ‘fullscan’
results
This is the side effect of updating statistics with fullscan on a weekly basis, it becomes more of a guessing game when your statistics are going to be automatically updated.
As mentioned in the comments and since ...
1
Which index to use?
While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See:
How to search a table with 80 million records faster?
However, your predicates strike me as pretty selective:
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND ...
Top 50 recent answers are included
