Hot answers tagged

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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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() - ...


2

type int(11) NOT NULL COMMENT '1. chatter, 2. marketing', -- Change to TINYINT UNSIGNED. This will save 3 bytes per row. Ditto for several other fields. And for those fields that are also in indexes, especially the PK, that "3" gets multiplied. bigint(20) unsigned -- If you are not expecting more than 4 billion, save 4 bytes each by switching to INT ...


2

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


2

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


2

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


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 ...


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

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 ...


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 ...


1

Query #1: You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the ...



Only top voted, non community-wiki answers of a minimum length are eligible