Skip to main content

New answers tagged

3 votes

Automatic index creation - too many

In analyzing the indexes, some of them differ only in the 'included' columns while the index columns were same This auto-indexing feature can be good in a situation if you don't have any DBA (...
Aleksey Vitsko's user avatar
1 vote

How a B-Tree index on a columnar table would look like?

This answer is for PostgreSQL specifically. A B-tree index on a table that uses columnar storage will look just like any other B-tree index. The leaf nodes reference a table row. In PostgreSQL, the ...
Laurenz Albe's user avatar
  • 53.9k
1 vote

Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint

Enable the option "include actual execution plan" in MSSQLM. Then execute your last two statements again. Look at the properties, outputlist of the rightmost operations. You will see that ...
HHM's user avatar
  • 11
3 votes
Accepted

What is the impact on an index when I am widening a key varchar column

200 million existing rows would not be updated and their EmployeeIDs would all still be less than 25 characters" We would just be adding a few thousand new rows that may have EmployeeIDs ...
Aleksey Vitsko's user avatar
0 votes

When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?

I am not sure what are you trying to do. If you generate a "path" like this through CTE, what is the point of searching through it??? If you generate such path from a real data in a real ...
White Owl's user avatar
  • 876
0 votes

Creating indexes before bulk-loading data is weirdly much faster than vice versa

-- Configuration (optimized for bulk loading) ALTER SYSTEM SET archive_mode = off; ALTER SYSTEM SET max_wal_senders = 0; ALTER SYSTEM SET wal_level = minimal; ALTER SYSTEM SET fsync = off; ALTER ...
warashi nguyen's user avatar
0 votes

Postgres - Improving multi-column GIN text search performance

Your index doesn't look very sensible to me. A multicolumn GIN index is pretty much identical to multiple single-column GIN indexes. It isn't like a Btree index, where selectivity on multiple ...
jjanes's user avatar
  • 40.3k
-1 votes

How to know when/if I have too many indexes?

I am using this query to find the tables with the highest number of indexes: SELECT Object_Name(object_id) as 'Table', COUNT(*) as NrOfIndexes FROM sys.indexes group by object_id order by NrOfIndexes ...
Balazs Kiss's user avatar
0 votes

MongoDB createIndex compound unique index on array field

See Create a Partial Index The partialFilterExpression option accepts a document that specifies the filter condition using: equality expressions (i.e. field: value or using the $eq operator), $...
Wernfried Domscheit's user avatar
11 votes

Does Ordering of Columns in INCLUDE Statement of Index Matter?

For index key columns the column ordering is significant. For included columns on the whole it doesn't matter. The order that the non key columns are stored on the index leaf pages won't affect the ...
Martin Smith's user avatar
  • 85.4k
2 votes

When is re-indexing triggered after multiple updates

The index will be modified right when you insert the row, and there is nothing you can do about that. GIN indexes have a “pending list” that stores new entries without actually adding them to the ...
Laurenz Albe's user avatar
  • 53.9k
1 vote

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

SQL Server and Oracle are different products. SET IDENTITY_INSERT table_name OFF is SQL Server syntax which has no equivalent on Oracle , the most similar might be generated by default as identity ...
Ergest Basha's user avatar
  • 4,000

Top 50 recent answers are included