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 ...
5
As someone who used to try to keep columns together like this, I highly suggest that you go with option #2.
The debatable upside of keeping the columns visually together (in the SSMS column list / when you SELECT * from the table) is absolutely dwarfed by the downsides of option #1.
As the tables get larger, doing this "copy and replace" operation is ...
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 ...
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™
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 ...
3
Please edit your question, rather than posting answers to it that don't answer it.
If you create an index on the expression substring(old_value,1,1024), then that index can only get used if you query involves substring(old_value,1,1024).
While it is theoretically possible to prove that old_value='foo' implies that substring(old_value,1,1024)='foo' (and ...
3
The most common approach would be to just add the column to the table and accept that the columns in the table aren't going to be grouped together by function.
First off, depending on exactly what we're talking about, having a bunch of similarly named columns may imply that you need to pause and consider refactoring your data model. If table 'X' actually ...
2
Historically, MySQL has kept the relevant algorithms simple, even if less than optimal. In particular, OR has been poorly optimized. 5.7 and 8.0 have new code, but I don't know if your examples have improved any.
EXPLAIN FORMAT=JSON sometimes gives clues. Also, see the "Optimizer Trace".
If you have sample data, use the Handler counts to deduce whether ...
2
But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part.
It is a snippet problem.
show index from test
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----...
2
I get a freshly built index of 256MB with that size of table. Is your index freshly built? Was the table freshly analyzed just before the build (the index is pre-sized based on the estimated rows in the table). What is your distribution of duplicates like?
Things are stored with minimum 8-byte alignment, so a hash index tuple is 16 bytes even if it ...
2
It is unusual to index such a long column entirely.
Three ideas:
Modify the query like this:
WHERE substring(old_value, 1, 100) LIKE substring(pattern, 1, 100)
AND old_value LIKE pattern
(pattern here would be the pattern string, something like 'string%'.)
Then a b-tree index on substring(old_value, 1, 100) can be used (if the pattern doesn't start ...
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 ...
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
--------+--------+---------
...
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 ...
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 ...
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 ...
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 ...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
