New answers tagged

2

Your key_buffer, innodb_buffer_pool and innodb_log_buffer together already exhausts the available memory in a a t2 instance. If you also consider the per thread allocations (>3M) * 70 (max connections) the possible usage is growing by another 210M minimum. You have 270MB InnoDb data. If you don't expect it to grow significantly over time almost 600MB ...


1

Assuming you want what your current query does (which seems different from what your description says). You ask: I think that problem is with filter but how to make it use index? Your query plan shows that Postgres is already using indexes for every step of the way. The added FILTER step only filters rows according to your additional predicates. I ...


0

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances Check innodb_buffer_pool_instances with: mysql> SHOW GLOBAL VARIABLES LIKE ...


3

Your basic question seems to be "Why" and I think you might find the answer about minute 55 or so of this Great presentation by Adam Machanic at TechEd a few years ago. I mention the 5 minutes at minute 55 but the whole presentation is worth the time. If you look at the query plan for your query I am sure you will find it has Residual Predicates for the ...


-6

this is just the original formatted DECLARE @Status INT = NULL, @IsUserGotAnActiveDirectoryUser BIT = NULL SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName], [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser] FROM Employee WHERE (@Status IS NULL OR [Status]=@Status) AND ( ...


0

Before we question whether index seek is preferred over index scan, one rule of thumb is to check how many rows are returned vs the total rows of the underlying table. For example if you expect your query to return 10 rows out of 1 million rows, then index seek is probably highly preferred than index scan. However, if a few thousand rows (or more) are to be ...


3

Disclaimer: Some of the stuff in this answer may make a DBA flinch. I'm approaching it from a pure performance standpoint - how to get Index Seeks when you always get Index Scans. With that out of the way, here goes. Your query is what's known as a "kitchen sink query" - a single query meant to cater for a range of possible search conditions. If the user ...


11

I don't think the scan is caused by a search for an empty string (and while you could add a filtered index for that case, it will only help very specific variations of the query). You are more likely a victim of parameter sniffing and a single plan not optimized for all of the various combinations of parameters (and parameter values) that you will be ...


0

The solution I implemented for this was to add a plpgsql function which ran each individual query and output the results as a table. I defined an output record type and used this function: DROP FUNCTION coeus.getexonvcf(text); CREATE OR REPLACE FUNCTION coeus.getexonvcf(text) RETURNS SETOF coeus.exonvcf AS $BODY$ DECLARE r bigint; s coeus.exonvcf; ...


0

I'd suggest using a CTE in this instance. Something that looks like this: with exons (genename, exonnumber, crom, exonstart, exonend, padding, direction) as (select genename, exonnumber, crom, exonstart, exonend, padding, direction from exons where enename = 'NM_001037501') select * from vcfentries vcf join exons exo using (chrom) where ...


2

The first thing that comes to mind is outdated statistics, not the fragmentation of the index as such. Right after the index is (re)built, the statistics associated with the index is accurate; the histogram range covers all values. As data changes in the table the statistics is not updated immediately. I don't remember now the exact thresholds, i.e. how ...


4

If the version was 9.3 or newer, I would try this rewrite of the query: SELECT w.stamp, w.column1, w.column2, w.column3 FROM "table_name" AS w JOIN LATERAL ( SELECT p.column1, p.column2, p.column3 FROM "table_name" AS p WHERE p.stamp < w.stamp AND p.stamp >= '2015-12-01'::timestamp ORDER BY p.stamp ...


1

There's probably no better way to get the expected result. Some remarks: You don't have to repeat the w.stamp BETWEEN '01.12.2015 00:00' AND '23.01.2016 00:00' in the outer query. Are you sure that a missing w.obj_id = 42 is correct? If this was part of the Derived Table the existing index would be useful, otherwise you need one on stamp. For this query ...


5

AFAIK the optimizer is not aware of index fragmentation. This can be a problem if it picks a plan that scans a fragmented index. The optimizer is aware of the allocated data size, though. If the index pages have a lot of free space (possibly due to internal fragmentation) this makes the index less likely to be used. 50% empty space means twice the amount of ...


3

SQL Server 2012 introduced some missing Windowed Aggregate Functions including LEAD & LAG, e.g. Window Functions in SQL Server: Part 2-The Frame or How to Use Microsoft SQL Server 2012's Window Functions LAG allows to access data from previous rows without using a cursor: SELECT ... FROM ( SELECT ... DATEDIFF(second, dtUTCDateTime, ...


2

As dnoeth mentions, you should be able to use lag to look at the previous value: SELECT ... , DATEDIFF(second, dtUTCDateTime, prev_dtUTCDateTime) AS DateDiffSeconds FROM ( SELECT ... , LAG(dtUTCDateTime) OVER (ORDER BY dtUTCDateTime) AS prev_dtUTCDateTime FROM VehicleMonitoringLog Where dtUTCDateTime > GetDate() - ...


5

Your CTE-based approach with window functions is a very good start. There's another, even more suitable window function that you could use: LAG(). Here's how: SELECT iVehicleMonitoringId AS CurrentID, LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, iAssetId AS CurrentAsset, LAG(iAssetId, 1) OVER (ORDER BY ...


-3

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. ...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


10

Here is what I do in such cases, usually some of this helps: Look at the whole query and try to remove unneeded tables from it. Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS. Try to increase planner constants so the server can put more effort into planning phase. You can do ...


0

Full text search fits more into your scenario. A full text search uses inverted index - think of it as an index structure that stores words or number mapping to its location in database. There are good amount of changes in sql server 2012 for full text search. Check Full-Text Search (SQL Server) A LIKE query against millions of rows of text data can ...


0

A leading % kills use of an index Most users want autocomplete based on first characters anyway Most users don't care about more than 1000 Have an index on city select top (1000) city from citylist where cityname like citynamevar + '%' order by city; If you can have them select a state from pull down select top (1000) city from citylist where ...


0

Have you thought about restricting the search in the UI? Don't start the autocomplete query until after they've typed 3 or 4 characters. Then instead of looking for 6000 A*, the first jump would be a fraction of that number still. Also, @Jonathan Fite 's comment about removing the leading % is also a good way to reduce time. Unless, your users start ...


4

An index on (practice_id, client_data_provider_id, date_scheduled) would be better than the one you have (practice_id, date_scheduled, client_data_provider_id), for this particular query. Notice the difference in order. When there are multiple equality (=) conditions and one range condition (>=, >, between, etc) in the where clause, it's better to ...


2

For just the two pages, a compromise could be: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) s(source_id) , LATERAL ( SELECT * FROM posts WHERE source_id = s.source_id AND deleted_at IS NULL ORDER BY external_created_at DESC LIMIT 200 -- ...


1

34 tables is a lot. Over-nomalization Many-to-many mappings that should be 1:many Excessive use of "added by" Use minimal datatypes Some examples: Because of the nested nature of districts-cities-states, and the fact that city and state names "never" change, there is no need to carry that normalization past district (or maybe even area). I suggest it ...


0

1) left join is slower than inner join. It is first reason. The second add special indexes for optimization query. Use explain plan of query 2)if you want to use result table then I recommend you use material view. It will be faster and you can don't think about length of fields. p.s. View is not a table so you will save you time instead your insert in ...


2

The first solution is clearly more efficient than the second one, because you don't need to do the join (every join slows down a query, more or less depending on the access plan generated by the system). More, I think the second solution has some problem, since from the schema it seems that you are using the column id inside mssg_grp as foreign key ...


8

First, as you noticed, SQL Server will automatically create missing statistics (where possible) when compiling an execution plan, if the database option AUTO_CREATE_STATISTICS is on. When statistics are not available, the default guess for an unknown inequality predicate is 0.3 (30%). When you use a local variable, the value in the variable cannot be ...



Top 50 recent answers are included