Tag Info

Hot answers tagged

4

Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge): Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to ...


4

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID(). You are currently inserting around 4,500 rows into the table per day. The table has around 6.3 million rows at present. That's around 0.7% of the table per day. Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day ...


4

Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says: Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Which - if you invert it - states that an ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...


3

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index ...


2

It depends on the method you're using to alter the column. We'll start with a simple table: CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200)); CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName); Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) ...


2

Index reorganize does not touches statistics so there is no chance for causing recompilation. Since when index is rebuilt with full scan stats are also updated for the column this can trigger recompilation as statistics change.


1

I would rewrite the query as following SELECT DISTINCT id FROM ( SELECT id from patient WHERE company_id=1 AND name_last LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_first LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_remote LIKE 'peter%' ) A; I would also change the indexes as follows ALTER ...


1

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%. Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. ...


1

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really ...


1

VACUUM ANALYZE makes the difference in your example. Plus, as @jjanes supplied, the additional statistics for the functional index. Per documentation: pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made ...


1

Looking at the original query SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp) It appears that you are looking for counts for just today. In that instance, why use GROUP BY at all ? You should run SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE(); OK, let's get a little more ...


1

First create a Calendar table (a table of dates) as described in this answer; then extend it by adding and populating Year, MonthName, DayOfMonth, and Tomorrow columns in that table and add a unique index on it by (Year, MonthName and DayOfMonth), and another unique index on it by the base column (Date) - the Primary Key. Now you can generate your reports ...


1

You could create an additional column, date, which stores the function date(timestamp) on insertion. This won't make the group by extremely efficient, but it can avoid the temporary table. The second problem is the range + GROUP BY, which would make an index on (timestamp, date) useless (BTREE limitations). You can create just an index on (date) or better, ...


1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


1

It is not doing an extra bitmap scan. It is doing a bitmap scan instead of the regular index scan. Why is it doing the bitmap scan instead? Because it thinks it will be faster that way. Without the LIMIT and the ORDER BY, it anticipates that using the bitmap scan will let it do the IO on the table heap in a more efficient manner. You can see if ...


1

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms. As per Verifying Security Access with Auditing chapter of Database Security Guide, there are several types of auditing, but Standard Auditing is probably the ...


1

Index definition Your multicolumn index generally looks good. If all or most of your queries use order by score desc, id desc, it would be a bit more efficient to define it with matching sort order and a simpler condition: CREATE INDEX prod_category_score_id ON products(category, score DESC, id DESC) WHERE active; WHERE active = TRUE is just a more ...


1

[Note: also answered at answers.SQLPerformance.com.] These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned. Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic ...


1

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all. Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) ...



Only top voted, non community-wiki answers of a minimum length are eligible