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
0
votes
1answer
53 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 ...
1
vote
1answer
20 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,...
0
votes
2answers
36 views

Data structure and design for statistics and reporting

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 ...
-1
votes
1answer
19 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, ...
-1
votes
0answers
16 views

How does SQL server perform the sampling when updating statistics? [duplicate]

To update statistics we can either do a full scan or sample. This question is about the later- sampling. I want to understand how sql server peforms the sampling. Does it randomly choose a set of n ...
-1
votes
0answers
49 views

Update statistics and Index Optimize jobs are taking a long time

My SQL Server 2014 Enterprise Edition has 256 GB RAM, 8 vCPU and the database is about 2.5 TB in size. The weekly DBCC CHECKDB job WITH PHYSICAL_ONLY is taking over 4 hours. The weekly Index Optimize ...
1
vote
1answer
36 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....
1
vote
0answers
52 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 ...
0
votes
1answer
71 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
2answers
23 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
votes
1answer
37 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
1answer
137 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,'...
0
votes
0answers
179 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 ...
2
votes
1answer
119 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 ...
1
vote
1answer
65 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 ...
1
vote
2answers
74 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 ...
1
vote
2answers
141 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 ...
2
votes
1answer
41 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 ...
0
votes
1answer
201 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(...
1
vote
1answer
42 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 ...
0
votes
1answer
39 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 ...
1
vote
1answer
55 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 ...
2
votes
0answers
58 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 ...
0
votes
2answers
101 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 ...
8
votes
2answers
318 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 ...
3
votes
1answer
86 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 ...
0
votes
1answer
677 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 ...
0
votes
0answers
37 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 ...
0
votes
1answer
24 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 ...
0
votes
0answers
33 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 ...
0
votes
1answer
32 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 ...
5
votes
1answer
215 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 ...
0
votes
1answer
70 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 ...
7
votes
3answers
531 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 ...
2
votes
0answers
43 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 ...
2
votes
1answer
66 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). ...
0
votes
1answer
29 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 ...
0
votes
0answers
35 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] [...
3
votes
1answer
135 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 ...
1
vote
2answers
61 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 ...
2
votes
1answer
302 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 ...
1
vote
1answer
511 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 ...
0
votes
2answers
150 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
1
vote
0answers
34 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
27 views

can't find column stats for newly created table

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached. database current settings are: I started with ...
1
vote
1answer
36 views

SQL Server: How to get total number of backups done in a month?

I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any. Is there a simple query that could give me this, ...
3
votes
2answers
71 views

Why is creating and dropping a random index fixing this performance problem?

I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
1
vote
1answer
140 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
1
vote
0answers
27 views

Index Exists, data is present, but statistics are empty. Why? [duplicate]

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is ...
-3
votes
2answers
507 views

Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?

I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table. The query only involves 3 tables (each ...

1
2 3 4 5
8