The Stack Overflow podcast is back! Listen to an interview with our new CEO.

New answers tagged

2

I had a similar need recently. The answer by KASQLDBA just pulls out the first missing index from the plan. There could be multiple. To that end I ended up using the following (I wanted the info at stored procedure level - use sys.dm_exec_query_stats instead of sys.dm_exec_procedure_stats if this is not desired) WITH XMLNAMESPACES (DEFAULT 'http://...


0

Have the primary key in place, but not the secondary keys. When finished loading, add all the secondary keys in a single ALTER statement. Caveats: I have not done that particular task, so I dont speak from experience. There have been changes over the years in how ALTER works. Doing multiple alters at once used to be optimal. But with online/instant/etc ...


0

This is straightforward enough and more efficient: SELECT school, CASE WHEN students > 1000 THEN 'SMALL' WHEN students > 10000 THEN 'MEDIUM' WHEN students > 100000 THEN 'LARGE' END as size FROM ( SELECT school, COUNT(*) as students FROM tbl_students ...


2

PostgreSQL can only use an index to count the number of rows if the table has been vacuumed recently so that most table blocks are marked "all visible" in the visibility map. Otherwise it has to inspect the table to check ifbthe row is visible or not, and then an index scan is more efficient. I think that the solution is not to count the rows. For a cheap ...


1

Indexes use space, and a 16 byte key is nothing to worry about. So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan. I see two options: In addition to the ...


0

Rows removed are really high, so it would be first indication for having index on report_date. But if your data has very cardinality for report_date then index selection may differ. Did you try adding index for just report_date order by desc nulls last? One of the merges is using disk which is costly. So adding the index on report_date would help. Also ...


1

The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity. The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to ...


0

I have seen this before on a badly designed system where the primary key was a compound and one column of it was updated quite frequently on the day it was created. This lead to index fragmentation by the evening (it was a 24/7 system) hours before index rebuilds were scheduled. At that point SQL stopped using the best query and slowed down dramatically even ...


1

Casts from text to timestamp without time zone are handled by calling the type input function timestamp_in, which is STABLE. The reason is that timestamp_in supports some other formats too: SELECT 'now'::timestamp; timestamp ---------------------------- 2019-10-09 09:53:32.026673 (1 row) If you know that your input is always an ISO ...


0

Try create table t ( data jsonb, data_timestamp TIMESTAMP GENERATED ALWAYS AS ((data->>'timestamp')::timestamp) STORED ); create index on t(data_timestamp); From comments: I also tried that (I forgot to mention that in the question), that gives the same error: ERROR: generation expression is not immutable. If so the only way which I ...


5

As per my understanding in above scenario we do not need order by as index have those columns already sorted That understanding is incorrect, and that plan shows one reason why. A parallel index scan doesn't output rows in index order, as each thread reads at a different location in the sort order. You can't expect rows in any particular order without an ...


0

not sure this help but in my case have table with 3mil row. very simple join or union or loop queries all very slow.. for only 20 row results affter test and test I just put a simple command to query FORCE INDEX (one_colum_indexed) which one_colum_indexed you has to test one by one if your table have multi index field, to look for which one best. all ...


0

As mentioned in the comments there are various factors that could cause fragmentation, and following are the areas that i can think of in your case. Fill Factor Seems it's already lower than normal on particular index. However, consider fine tuning with it (in development server) if following options are not applicable in your scenario. Auto Shrink Verify ...


2

What you described is normal behaviour. create table t1 as select * from dba_objects; create table t2 as select * from dba_objects; delete from t1 where object_id is null; delete from t2 where object_name = 'T1'; alter table t1 add primary key (object_id); alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id); create index ...


0

But it’s safe to assume PK is indeed present ? Yes. (The following applies only to InnoDB; other engines probably differ.) A copy of the PK column(s) is tacked on the end of regular indexes. FULLTEXT and SPATIAL work differently, so this discussion may not apply. There has been some chatter about whether the FK columns are redundantly added, or only if ...


3

Looks like you want a (LEFT) JOIN LATERAL - the standard SQL equivalent of (OUTER) APPLY in SQL Server, as a_horse called to mind: SELECT t.ticketid, t.ticketsummary, ta2.* FROM tblTickets t LEFT JOIN LATERAL ( SELECT ta.allocationid FROM tblassignment ta WHERE ta.ticketid = t.ticketid ORDER BY ta.allocationid DESC NULLS LAST -- see ...


0

The behaviour of disabling indexes changes depending on if the index is A) nonclustered or B) clustered For nonclustered indexes Disabling a nonclustered index removes the index data pages. Disabling a nonclustered index will deallocate the index pages – the space is freed in the database. Source What remains when disabling the index? The index ...


0

I will be more specific. You can use a script like this: DECLARE @sql NVARCHAR(MAX); SELECT @sql = ( SELECT 'IF EXISTS(SELECT * FROM sys.indexes WHERE name=''' + i.name + ''' AND object_id = OBJECT_ID(''[' + s.name + '].[' + o.name + ']'')) drop index [' + i.name + '] ON [' + s.name + '].[' + o.name + ']; ' FROM sys.indexes i INNER ...


0

No. Script out the disabled and unused indexes then drop them. Save the script of index create statements in your flavor of Source control just in case you need them.


2

You would have to create the trigram index on the complete expression left of the LIKE operator. The lower is unnecessary, and you should omit it.


2

This is not specific to MySQL, it is about B-tree indexes in general. Leaving aside the implementation details, you can imagine a B-tree index as a sorted list of the indexed columns with a pointer to the table. So if you imagine a two-column index on (num1, num2), it would look somewhat like this: num1 | num2 | pointer --------+--------+--------- ...


1

It will be possible in upcoming PostgreSQL 12 release (should be released on October 3, 2019). SELECT now()::TIME(0), a.query, p.phase, p.blocks_total, p.blocks_done, p.tuples_total, p.tuples_done FROM pg_stat_progress_create_index p JOIN pg_stat_activity a ON p.pid = a.pid; See the docs for pg_stat_progress_create_index view and ...


4

I agree with Erik, but answering your question the error was in the quotation marks in the STUFF and here AND ISNULL (DUPE1.include_column_list, '') = ISNULL (DUPE2.include_column_list, '') You did not have the quotation marks correctly, remember that when you intermix texts all the '' must be like '' '' DECLARE @db_name AS nvarchar(max) DECLARE c_db_names ...


0

Create calculated field HOURpkey1 TINYINT AS (HOUR(pkey1)) Make it either STORED or VIRTUAL - think by yourself what is better for you. Create index KEY idx_pkey2_pkey3_HOURpkey1 ON table (pkey2,pkey3,HOURpkey1) Any fields order is possible and safe for the query in question, think by yourself what is the best. If the amount of records in output is ...


1

Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is: First priority should be given ...


4

Rather than hack away at a script you don't understand well enough to troubleshoot, why not use something tested and understandable, like sp_BlitzIndex? If you run EXEC sp_BlitzIndex @Mode = 4, @GetAllDatabases = 1; it will go through and diagnose all sorts of index issues beyond what you're looking for in that Script You Found On The Internet™


0

As @bma explained in a comment, your index will work as-is, if you add double parentheses around the entire JSON definition: CREATE INDEX user_reputation_idx ON users((("user"->>'reputation')::int)); Make sure you consistently use the JSON text notation [->>], not the JSON object notation [->], in the index and when referencing the field in a query, ...


0

Here is a version based on the one by E.Mantovanelli in this thread. This corrects an issue where a unique index did not include the key word UNIQUE in the resulting script. It also adds parameters so a table can be created without non-Clustered indexes or you can only script the non-clustered indexes. I use this to Create a stage table, load it add the non-...


-3

Any Unique Key you create has no need of a PRIMARY key. Over 8.5 years ago, I answered the post Why do primary keys have names of their own?. I described how a Primary Key is really no different from a Unique Key because all Unique Keys (properly known in Database Theory as Candidate Keys) define a level of uniqueness for each row in a table. For example, ...


Top 50 recent answers are included