Tag Info

New answers tagged

0

Almost positive you will get an index seek on UserId without the IX. Test.


1

From your query plans, it looks like you're comparing ints to ints in the first query plan, and int to numeric in the second plan. Your first compare: Index Cond: (("timestamp" >= 1431100800) AND ("timestamp" <= 1431108000)) and timestamp >= 1431100800 and timestamp <= 1431108000 In the second query, it's numeric values: Filter: ...


0

A query use the index in the column order (UserId first then RoleId). Without an index on RoleId, it will scan the clustered index. Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId. Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if ...


-1

I know the reason now. I need to vacuum the tables before using index-only-scan, otherwise it will not choose to use index-only scan by default. If i force it to use index-only-scan, it will fetch data from table for each tuple scanned, which can cause great cost.


1

In general, where you put the filters makes a difference. While Tom V says the Optimizer will recognize that the queries are the same and come up with the same plan, that is not always true. It depends on what version of SQL you are on, how complex your query is, and how important to the overall batch the Optimizer determines the query is. The Optimizer ...


2

In simple cases, it will be the same. However, I have seen very complex queries with several joins have significantly different plans. A recent one I was working on started with a table that has close to 6 million rows joined to about 20 different tables. Only the first join to this table was an inner join, all others were left outer joins. The filter in the ...


-1

With INNER JOINs, it is a style issue. However, it becomes much more interesting with OUTER JOINs. You should explore the differences between queries with OUTER JOINs and conditions in both the ON clause and the WHERE clause. The result-set is not always the same. Is, for example, OUTER JOIN dbo.x ON a.ID = x.ID ... WHERE x.SomeField IS NOT NULL the same ...


1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


5

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb. It gives you better advantage when tempdb is on a different set ...


3

Erwin describes a method of creating an index that violates PostgreSQL's assumptions about the index, producing results that are likely incorrect and do not match the underlying table. That's one kind of corruption. Another kind of corruption is where an index has blocks that are simply invalid - zeroed out, replaced with random values, etc. Most likely ...


1

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research. Our heuristic to determine when maintenance is needed Our primary goal is to retain consistent full-text query performance as data evolves in the ...


3

Erwin's answer does a good job of answering the question as originally stated, however you added the comment: I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID' Assuming you are talking about the indisvalid column in pg_index, or some ...


3

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked: One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday: CREATE OR REPLACE FUNCTION f_fake_immutable_ts() RETURNS timestamp LANGUAGE ...


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


6

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done. These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly ...


1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


3

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql ...


2

This is quite a common query (pardon the pun! :-) ) from people running queries which perform full table scans (FTS), when the poster feels that the system should make use of the index(es). Basically, it boils down to the explanation given here. If the tables are so small, the optimiser will say that "it's not worth the bother of going to the index, doing a ...


0

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.


0

What is the output of: select * from v$database_block_corruption? Unlike regular indexes, LOB indexes can't be dropped/ recreated, because you would have to drop the column instead. So, assuming your backups are valid, you can attempt to perform block media recovery (BMR): http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmblock.htm


2

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data. In that case, separating that 95% of the data is way to go. If you want to stay with SSIS, the obvious solution is to redirect those records to a another flat file (can also be a separate db) during import. Specifying this flat file ...


7

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc? No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle. Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it. That being said, SQL Server exposes a ...


4

I have a broad question regarding SQL Server... Depending on which server version you have access to, the tools vary. Microsoft has an entire documentation section on optimization tools for each of the different server versions. Despite the differences, the fundamentals are the same across most versions. Missing indexes, for example, are addressed in ...


1

Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan. Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Index Seek: ...


1

The only situation where an index with full key cardinality = 1 will help is when you are looking for something that does not exists. By using such index you can quickly conclude that nothing will match your predicate, whereas without the index you will have to scan the table. If you have a large table and you frequently look for things that does not ...


1

Here's some explanation of which index you could use and when: First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes ...


1

It's a foreign key error (Error: 1025 | errno: 150) you can find out using perror: root@onare:/home/onare# perror 1025 MySQL error code 1025 (ER_ERROR_ON_RENAME): Error on rename of '%-.210s' to '%-.210s' (errno: %d) root@onare:/home/onare# perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed You can get more details about what ...


3

The index that will give you the most benefit is one on fldB, fldC, fldA desc or on fldC, fldB, fldA desc depending on your data. You'll need to check your data and keys to determine which column should go first. The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table ...


1

As only fldC & fldB are in the filter criteria, an index with these two columns on the left side will be right. Try to index on (fldC, fldB, fldA), this should do the trick mostly.


0

The query planner seems to think that created is more selective than token. Are your table statistics up to date? Check the last_analyze column in: SELECT schemaname, relname, last_analyze FROM pg_stat_all_tables WHERE relname = 'logs' To really force the issue, you can use a temporary table: begin; create temporary table tmp_logs (created ...


0

From the Notes on Function-based Indexes section of the user documentation for CREATE INDEX: When you subsequently query a table that uses a function-based index, Oracle Database will not use the index unless the query filters out nulls. However, Oracle Database will use a function-based index in a query even if the columns specified in the WHERE ...


1

I think I am starting to understand. When I asked you to run SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1; You said it was 2015-07-13 15:11:56 which you have in your WHERE clause When you did the query select sum(diff_ms) from writetest_table; It performed a full table scan of 35.8 million rows. When you did the query select ...


4

For the specific query: select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56' ; -- use single quotes, not double an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index: ALTER TABLE writetest_table ADD INDEX ...


2

I think you already more or less answered your own question. While a GIN index can speed up searching inside an array quite significantly, you have to be aware that relational database engines are designed to do joins very efficiently. Also, when you need the actual tags, you will have to join the tag table regardless of you use the array approach or ...


5

Neither MySQL nor the siblings (MariaDB, Drizzle, etc) have implemented partial indexes. What you can do, with this restriction in mind: a) make a simple (not partial) index on (is_active, measurement_id). It will be used in queries where the partial index would. Of course if the is_active column is 3% True and 97% false, this index will be much bigger ...


2

I wouldn't say that index 2 is a subset of index 1. An index that had the columns UserId, InstanceId would be. This could still be useful to queries that do not require the created column as they would be accessing a more narrow index which would require less IO and memory. Your best bet is to check if your indexes are actually being used by queries. ...


2

Based on: WHERE [Entity_Identifier] = @EntityIdentifier AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId ORDER BY [Message_Sequence] ASC ...the best thing is to have an index on ([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress' As Status is filtered with a constant, a WHERE clause is going to be useful ...


-1

In general, index are used during row selection. So, creating an index on combination of fields in the where clause, such as Entity_Identifier, Status, and Process_Id would help the performance. The exact field combination would depend on the nature of data you have in your table. You are right, creating an index on the Message_Sequence would not help the ...


0

If your base document with all the data is 300 bytes, and worst case scenario for storage usage scenario, your indexes are all 300 bytes each but just sorted differently, would get you 900 bytes of memory used per document for indexes and 300 bytes for the base. 1440 events * 1200 bytes (Base document + 3 indexes each at 300 bytes) = 1728000 bytes ...


0

Plan A: Use INSERT IGNORE in your batched inserts. That way the dup keys do not cause trouble. Plan B: Insert into a table with INDEX, not UNIQUE. Then you can investigate the duplicates before deciding what to do with them. You understand that CHAR(15) utf8 occupies 45 bytes always? Perhaps VARCHAR(15) would be better? (Please don't quote the ...


0

There is no guarantee that the valueaaaaaaaaaaaaaaa-11111 in the message [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'mykey' is the value that actually causes the violation. Seems to be a bug in MariaDB and evtl. in MySQL.


1

Erwin, since this was our discussion in the comment thread from before, I decided to poke at it a little further... I have a very simple query from a reasonably sized table. I typically have sufficient work_mem, but in this case I used the commands SET work_mem = 64; to set a very small work_mem and SET work_mem = default; to set my work_mem back to ...


4

As @Chris commented correctly on the referenced question: a little investigation seems to indicate that the recheck condition is always printed in the EXPLAIN, but is actually only performed when work_mem is small enough that the bitmap becomes lossy. Thoughts? http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com While this is ...


0

Unclear what you want to do. If you want to see rows with keyword in both d and somewhere in a,b,c, then your AND is a good way to go. But if you want keyword to be in any of a,b,c,d, then add a third index FULLTEXT(a,b,c,d) and change to MATCH(a,b,c,d) AGAINST('keyword') For further discussion please specify whether you are using InnoDB or MyISAM; ...


2

Unfortunately, this is how MySQL Query Optimizer treats FULLTEXT indexes. When a MATCH clause is the only clause in the WHERE, the index will be used. When used in conjunction with AND, the index may easily get overlooked. I wrote about this behavior before in Mysql fulltext search my.cnf optimization SUGGESTION : Rewrite the query as the union of two ...



Top 50 recent answers are included