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.
392
questions
0
votes
1
answer
38
views
Outliers produce a bad query plan
I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns:
CREATE TABLE document
(
id serial PRIMARY KEY,
organizationid INTEGER,
status_1 ...
1
vote
1
answer
37
views
Wrong cardinality estimation after gathering statistics ORACLE
We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like
select count(*) from my_table where ...
0
votes
2
answers
108
views
Use extended events to track autoupdate statistics on a specific table
I'm trying to find a way to track a table that has auto update statistics on, I'm trying to track how often it auto updates and at what intervals.
The table in question is used quite often and because ...
Golosie Emanuel
0
votes
0
answers
17
views
Mysql system tables showing incorrect cardinality for columns
I’m new to mysql. I have encountered a weird problem on our prod database. We have a table with primary key a and an index on some other b column. Below’s the query:
select b from my_table where a ...
- 348
1
vote
1
answer
103
views
Performance issue after migration to SQL Server 2019
I am troubleshooting a performance issue on a job that is taking 5h longer to complete on SQL Server 2019 compared to when it ran on 2012.
One of the problematic steps of the job is during a batch ...
- 13
1
vote
1
answer
621
views
Do statistics have an impact on delete performance?
Background:
I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records.
The table is a history table, and the application imports ...
- 113
0
votes
1
answer
132
views
Why does a SELECT query (Sch-S) get blocked due to an existing Sch-S lock held by index reorganize command?
SELECT query requires a SCH-S lock. This can be shared with other read queries.
UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries.
Link: https://www.mssqltips....
- 3,440
1
vote
1
answer
63
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'...
- 47
0
votes
2
answers
149
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
55
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 (...
- 520
3
votes
1
answer
163
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.7k
7
votes
1
answer
155
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,928
0
votes
0
answers
209
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
216
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
1k
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
97
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
856
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 ...
- 133
0
votes
2
answers
109
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
115
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 ...
- 523
0
votes
3
answers
583
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 ...
- 645
8
votes
1
answer
462
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?
- 83k
-1
votes
1
answer
112
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.8k
1
vote
2
answers
232
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
47
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
185
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 ...
- 283
-3
votes
1
answer
530
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,440
0
votes
2
answers
150
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
165
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
237
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,928
3
votes
1
answer
174
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,928
-4
votes
1
answer
119
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,440
1
vote
1
answer
387
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
1
vote
1
answer
3k
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
237
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, ...
- 557
0
votes
1
answer
493
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
2k
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
1k
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
236
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 ...
- 61
0
votes
1
answer
331
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
196
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
164
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
360
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
74
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
75
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,173
0
votes
1
answer
150
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
57
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
629
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
662
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
88
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
1k
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