Questions tagged [statistics]
Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.
385
questions
1
vote
1
answer
35
views
Should statistics be updated manually?
I'm currently considering using Ola Hallengren's Index Maintenance solution to update statistics once a day. What is the benefit of this solution if a database has AUTO_UPDATE_STATISTICS set to on?
I'...
- 23
0
votes
2
answers
47
views
Improving stats for a join to avoid Nested Loops with LIMIT
I have a main fact table t1 with ~6.9m rows, a second dimension table t2 with 1329 rows, and a third table t3 with ~6.9m rows (1-1 with t1, separate because it's produced by a different batch process)....
- 103
0
votes
0
answers
27
views
DBA_TAB_MODIFICATIONS out of whack in Oracle when moving partition or subpartition segments
I've observed that when moving a partition or subpartition of a table (ALTER TABLE xyz MOVE PARTITION abc), the row count of the moved segment gets reflected in DBA_TAB_MODIFICATIONS at higher levels (...
- 265
3
votes
1
answer
132
views
How is a statistics collection different to normal table stats?
I have a small table with few rows
create table dbo.p(i int);
insert dbo.p(i)
values (1), (2), (3), (4), (4);
I created statistics; no index, just the stats.
create statistics p_c on dbo.p(i) with ...
- 24.4k
7
votes
1
answer
133
views
SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values
I have a statistic on a Primary Key column in a table. When I update the statistic with the default options:
UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350
I get a histogram as follows (...
- 1,741
0
votes
0
answers
77
views
When to use sp_updatestats and when to use sp_updatestats with the resample option?
I am confused about the usage of RESAMPLE option and when to use it.
We had problems with a customer DB. One/some queries took longer as expected and the CPU usage on the machine was higher than in ...
- 230
3
votes
0
answers
163
views
Possible missing step in sampled statistics generation?
I've searched far and wide for information on why sampled statistics are less accurate, and broadly speaking every question has always been explained with the following generalizations:
sampled are ...
- 39
3
votes
1
answer
388
views
Update statistics on a 4 billion record table
On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records.
It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while ...
- 41
-1
votes
1
answer
60
views
DB2 Cardinality
In my tables, I have many statistics with -1 and I want to know how can I enforce DB2 to calculate these values.
Select FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD
...
- 1
2
votes
2
answers
291
views
What exactly is "only a read lock on the target table"
I'm trying to find information on whether it's safe to run ANALYZE on a large table that has stale statistics. The table is accessed in an "append-only" fashion and is constantly written to ...
- 123
0
votes
1
answer
49
views
IndexOptimize procedure doesn't obey @MaxNumberOfPages when only updating statistics
I'd like to use
EXECUTE [master].dbo.IndexOptimize
@Databases = 'mydatabase',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@...
- 126
0
votes
1
answer
79
views
Operational stats mention the same table twice, what does this mean?
While investigating why my application mentions deadlocking, I fell upon this question, whose answer mentions an SQL query, showing operational logs. However, the results of that query contain the ...
- 513
0
votes
3
answers
254
views
Running sp_updatestats probably overrides manually done UPDATE STATISTICS WITH FULLSCAN and ruins performance
We experience this on both SQL Server 2014 and SQL Server 2019.
We have an application, which loads some data into our database. At the end of this load process, the application runs sp_updatestats ...
- 635
8
votes
1
answer
446
views
Why are statistics slower to build on clustered columnstore?
Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?
- 80.3k
-1
votes
1
answer
77
views
queries with significant difference of estimated and actual number of rows - skew data - in the query plan
I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics.
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If ...
- 15.6k
1
vote
2
answers
143
views
How can I make queries on skewed datasets more performant on Postgres
I have a seemingly simple query that's unfortunately extremely slow.
I believe that I know why it is slow, but not how to make it fast, so I'd like to see how this could be improved.
let's call the ...
0
votes
0
answers
43
views
How to optimize execution plan for lopsided table
I've got a table with an index that's pretty lopsided, where a small number of values has a much higher count of rows than most of them. In the table there are a few hundred million rows - my index is ...
- 137
1
vote
0
answers
138
views
Postgres UUID correlation workaround
I have a table with a UUIDv4 field owner. The table has 10s of millions of rows, but only a few thousand unique owners. Owners typically create/update their rows in one burst, so their tuples are ...
- 253
-3
votes
1
answer
271
views
Why do indexes, statistics, and full text need to be rebuilt/updated/repopulated after migration?
I have created a new server, restored the system dbs and presented a copy of the data/log disks from the previous machine (or alternatively maybe I restore the user dbs using the .bak files).
Post SQL ...
- 3,225
0
votes
2
answers
113
views
Bad PostgreSQL planner decisions on column with uneven distribution
Facts
I have following simple table that represents financial transactions:
CREATE TABLE transaction (
"user_id" text not null,
"offset" serial not null,
"tx_data&...
- 3
0
votes
1
answer
104
views
Diagnose the exact cause of WAIT_ON_SYNC_STATISTICS_REFRESH
We are currently facing query timeout issues with our online API .
The related request is an ad hoc select query referencing two tables.
In Redgate, we see that each time the request is blocked, it's ...
4
votes
1
answer
188
views
How does SQL estimate the number of rows in a less than < Predicate
I have been doing some testing to try to better understand how SQL Server uses a histogram to estimate the number of rows that will match an equality predicate and also a < or > predicate
Given ...
- 1,741
3
votes
1
answer
142
views
Why Does SQL Server not have 200 buckets in the statistics histogram when there are >100k distinct values in the table
Given I am using the AdventureWorks2016 OLTP database why does the statistics histogram for the index PK_TransactionHistory_TransactionID on table Production.TransactionHistory only contain 3 ...
- 1,741
-4
votes
1
answer
56
views
Is the effect of statistics update immediate or does sql server need to be restarted?
Index statistics are utilized by sql server to derive the best plan for query execution.
Suppose I update the index statistics then can existing queries use the effect of updated statistics or does ...
- 3,225
1
vote
1
answer
273
views
Mysql.innodb_table_stats & mysql.innodb_index_stats not updating
I believe I have persitent stats configured correctly and there are records in INFORMATION_SCHEMA.STATISTICS table but the following tables have no data for many of the tables/indexes. mysql....
- 51
0
votes
1
answer
2k
views
SQL Update statistics with Fullscan and normal update without fullscan
What is the difference between SQL Server Update statistics with Fullscan and without Fullscan.
Is the Fullscan will improve the performance better that without fullscan option. How can we get the see ...
Surendhar
0
votes
3
answers
143
views
Do I need statistics in addition to an index?
Suppose I have a table with images that are supposed to go through several steps:
CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool);
INSERT INTO images (filename, ...
- 537
0
votes
1
answer
359
views
statistics on what queries were executed and how many times
I'd like to monitor how many times certain queries are executed. Take a look at the 3 queries below, the second and third are written as prepared statement.
SELECT COUNT(*) FROM TABLE1
UPDATE TABLE2 ...
- 253
3
votes
2
answers
1k
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 131
7
votes
1
answer
727
views
Why is statistics update slower on a clustered columnstore than on a rowstore?
I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
0
votes
1
answer
197
views
What do these weird errors logged mean?
Today, I got these errors logged for the first time:
stats_timestamp 2022-01-16 10:58:25.625142+01 is later than collector's time 2022-01-16 10:58:23.356175+01 for database 0
statistics collector's ...
- 51
0
votes
1
answer
197
views
Order of execution in Ola Index maintenance and Stats collection
I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance.
step 1.Perform all index maintenance activity for all required Indexes(...
- 1
0
votes
2
answers
165
views
Postgres 12 - under which cirumstances should I activate the query and index statistics collector
TL;DR - I don't have the collector activated (yet) and it seems to not be the default; how bad is this and should I activate it?
I've just created a new index on a table where a query was slow.
I've ...
0
votes
1
answer
117
views
Does the Query Optimizer use Incremental Statistics yet?
I was considering using incremental statistics in my data warehouse but I read an article by Erin Stellato that says the query optimizer doesn't use incremental statistics. The article was written in ...
- 37
1
vote
1
answer
303
views
Postgres creating 'histogram_bounds' statistics on JSONB column
I have two database tables (A and B), each containing roughly 1.6m rows, and each having the following structure:
CREATE TABLE tablename (
identifier text NOT NULL,
geometry geometry(GeometryZ,...
- 11
0
votes
2
answers
68
views
Data structure and design for statistics and reporting [closed]
I hope I will get luck describing what I am looking for, because This is some thing new for me and I am not sure if I will use the correct names and terms.
I have tables which contains a million of ...
- 101
-1
votes
1
answer
58
views
Why is the estimate (out of Stream Aggregate) so low?
I have a table like this:
create TABLE dbo.MyTable
(
TMyTableID bigint NOT NULL IDENTITY(1, 1),
LogArgumentID bigint NOT null,
LogID bigint NOT NULL,
LogTextID int NOT NULL,
FloatValue float NULL,
...
- 2,153
0
votes
1
answer
91
views
Oracle gather stats autotask being interrupted before maintenance window ends
a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way:
BEGIN
dbms_scheduler.disable(
name => 'WINDOW_NAME');
dbms_scheduler....
- 9
1
vote
0
answers
56
views
What is the best way to have actual execution plan and IO stats as values to insert?
We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
- 45
0
votes
1
answer
411
views
Wait state: Statistics
We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics.
According ...
0
votes
2
answers
322
views
When to analyze columns?
I have a table with millions of records. Before select, I run Analyze table (col1, col2).
We want to reduce cost, since a lot of IO operations happen in AWS Aurora.
Should I run analyze only when we ...
- 1
-1
votes
1
answer
55
views
ALTER INDEX ALL did not fix the problem, but deleting and recreating a non-clustered index does the trick. Why?
My SQL Server version is 2014.
The table has over 21 million rows. One of its indexes is a non-clustered index on an integer field that is referenced a lot.
I did a mass update that affected about 20%...
3
votes
1
answer
814
views
Autotask 'auto optimizer stats collection' and scheduler jobs logging
We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since
december Oracle has left more than 18 thousand of them with no statistics
renewed:
SQL> select to_char(last_analyzed,'...
- 31
2
votes
1
answer
690
views
Very bad query plan in PostgreSQL 9.6
I have a performance problem with PostgreSQL 9.6.17 (x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-39), 64-bit). Sometimes very inefficient query plan is chosen for ...
- 21
1
vote
1
answer
164
views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
- 131
1
vote
2
answers
334
views
Disable AUTO_UPDATE_STATISTICS
We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
- 11
1
vote
2
answers
3k
views
Postgres date_trunc quarter with a custom start month
I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01.
Is there any way ...
- 43
2
votes
1
answer
94
views
Why do SQL Server auto-created statistics sometimes include the primary key?
I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics.
However, today I came across some auto-created ...
- 133
0
votes
1
answer
1k
views
How do I make PostgreSQL include all the months where I did not buy anything? [duplicate]
I have this query:
SELECT avg(sum)
FROM
(
SELECT date_trunc('month', "timestamp"), sum(amount) as sum
FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%'
GROUP BY date_trunc(...
- 527
1
vote
1
answer
106
views
Row estimate more accurate for primary key column than for others?
Let's create two test tables in a PostgreSQL 13 database:
CREATE TABLE foo (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
value int NOT NULL
);
CREATE TABLE bar (
id bigint PRIMARY ...
- 367