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.

Filter by
Sorted by
Tagged with
-4 votes
1 answer
26 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 ...
user avatar
  • 2,076
1 vote
1 answer
26 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....
user avatar
  • 51
-1 votes
1 answer
45 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 ...
user avatar
0 votes
3 answers
46 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, ...
user avatar
  • 415
0 votes
1 answer
15 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 ...
user avatar
  • 233
2 votes
2 answers
71 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 ...
user avatar
6 votes
1 answer
260 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 ...
user avatar
0 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
48 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(...
user avatar
0 votes
2 answers
34 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 ...
user avatar
0 votes
1 answer
75 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 ...
user avatar
1 vote
1 answer
96 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,...
user avatar
  • 11
0 votes
2 answers
48 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 ...
user avatar
  • 101
-1 votes
1 answer
24 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, ...
user avatar
1 vote
1 answer
47 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....
user avatar
1 vote
0 answers
53 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 ...
user avatar
  • 45
0 votes
1 answer
153 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 ...
user avatar
0 votes
2 answers
24 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 ...
user avatar
  • 1
-1 votes
1 answer
43 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%...
user avatar
3 votes
1 answer
285 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,'...
user avatar
  • 31
0 votes
0 answers
443 views

SQL Analyze - dbms_stats consuming much CPU on Oracle Server

In one bigger DB with many user schemas and big tables I have the devs starting regulary the analyze select dbms_stats queries, that consume a lot of cpu when started for many users at once. These ...
user avatar
2 votes
1 answer
287 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 ...
user avatar
  • 21
1 vote
1 answer
97 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 ...
user avatar
  • 131
1 vote
2 answers
135 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 ...
user avatar
1 vote
2 answers
460 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 ...
user avatar
2 votes
1 answer
55 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 ...
user avatar
  • 133
0 votes
1 answer
425 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(...
user avatar
1 vote
1 answer
44 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 ...
user avatar
0 votes
1 answer
46 views

Using Postgres to return a selection based on frequency distribution

I am not even sure if this is a possibility so my apologies if I'm barking up the wrong tree; I am ignorant of Postgres's limitations as I am self-taught. This is hypothetical, but if I had a table ...
user avatar
  • 101
1 vote
1 answer
77 views

Questions on updating statistics and index maintenance jobs

Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions: For ...
user avatar
2 votes
0 answers
61 views

How do I measure the overhead of a Postgres extended statistics object?

Some solutions to poor query performance suggest creating extended statistics objects to help the query planner better understand the shape of the data. Because these statistics objects are not ...
user avatar
0 votes
2 answers
209 views

Person unique identification

I am working on a sports database. I have table for persons(players), teams, leagues etc. The problem is, how do I identify a person uniqely? I was thinking that a new person could be added by either ...
user avatar
8 votes
2 answers
352 views

Inability to query underlying table of a schema-bound view

I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE ...
user avatar
3 votes
1 answer
88 views

Query behavior - with regard to statistics

A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally ...
user avatar
  • 163
0 votes
1 answer
1k views

How to set statistics level of a table in postgres?

I know how to set statistics level for a particular column i.e alter table table_name alter column column_name set statistics 100; However, if I wish to set statistics level for a particular table or ...
user avatar
0 votes
0 answers
94 views

How to find the statistics level of a column in postgres?

I have altered the statistics level of column using below command. edbstore=> alter table orders alter column orderdate set statistics 100; From which table, i can find the statics level of all the ...
user avatar
0 votes
1 answer
26 views

Statistics not collected on Insert-Select

Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics ...
user avatar
  • 101
0 votes
0 answers
38 views

PostgreSQL JSONB Execution Plan with high Offset

How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below "Test Query", help me to clarify the ...
user avatar
0 votes
1 answer
36 views

index used when query process on oracle database

I a new one using and learn about oracle database. currently i have sql query like this select attend_code from attadanstatusdetail where attend_id = 'attendidcode001' and company_id = 168 currently ...
user avatar
  • 47
5 votes
1 answer
269 views

Statistics histogram AVG_RANGE_ROWS discrepancy

According to MS docs, the description for AVG_RANGE_ROWS is: Average number of rows with duplicate column values within a histogram step, excluding the upper bound. When DISTINCT_RANGE_ROWS is ...
user avatar
0 votes
1 answer
101 views

Query optimizer triggering statistics update seems to be slowing down queries

We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
user avatar
7 votes
3 answers
541 views

Consistent query performance over time

We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of ...
user avatar
2 votes
0 answers
47 views

Incorrect EQ_ROWS from low % sampled statistic (but not on all columns)

On a particular nullable guid column I am getting EQ_ROWS that are substantially incorrect (Table has 977 million rows). Default sample size is to blame - currently about .1% Here is a single row out ...
user avatar
2 votes
1 answer
126 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
user avatar
  • 63
0 votes
1 answer
37 views

Statistics Update Time is missing in Actual execution plan

I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the ...
user avatar
0 votes
0 answers
50 views

update stats on specific statistic not working?

I am using Ola Hallengren's maintenance solution and have come across something really strange. I grabbed the command from the log table as below: UPDATE STATISTICS [dbo].[APIActivityLog] [...
user avatar
3 votes
1 answer
181 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
user avatar
1 vote
2 answers
104 views

Why does TempDB spill happen even though statistics are correct?

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
user avatar
  • 1,163
2 votes
1 answer
376 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
user avatar
  • 21
2 votes
1 answer
1k views

Query performance degraded after upgrading Postgres

I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
user avatar
  • 1,028

1
2 3 4 5
8