Hot answers tagged

12

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch. More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the ...


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


8

I believe your estimates are wrong because of an estimator bug that swaps two of the DATEDIFF arguments. I talk about this here: Performance Surprises and Assumptions : DATEDIFF A workaround is to calculate the first day of 13 months ago without using DATEDIFF (2008+): DATEADD(MONTH, -13, DATEADD(DAY, 1-DATEPART(DAY,GETDATE()), CONVERT(DATE, ...


7

Well, I think you have some mixed concepts: An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the ...


7

You could add a calculated column to the table and build an index from the calculation. For instance, the table would be: CREATE TABLE dbo.InverterData ( InverterID bigint NOT NULL , TS datetime NOT NULL , ValueA decimal(18, 2) NULL , ValueB decimal(18, 2) NULL , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0)) ...


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


5

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently. This needs a few prerequisites, though: PostgreSQL version 9.2 or newer, as index-only scans appeared in this version the index supports the query (the order of the columns of the index decides ...


3

This is the part of the execution plan where you expect an index being used: -> Seq Scan on paid gap (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1) Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= ...


3

Having too many indexes can indeed cause performance problems. If many indexes have very similar statistics it is possible that the optimizer cannot reliably decide on the most useful choice of indexes. (I learned this when working with a database where almost every column was indexed.) In that case, we reduced the number of indexes significantly by ...


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


2

Trying to stay database-neutral: Reading, filtering Indexes radically speed up ordering and filtering operations on a table - often by a factor of 1000 times or more. Compared to a phone book, an index lets you look up a single person up directly, because it's alread sorted alphabetically. If the phone book were just an unordered list of a million names ...


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

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


2

Look at the names more carefully. pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing. The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.


2

Explain We would have to see table definition, cardinalities and the EXPLAIN output to be certain, but the reason is most likely this: Only your spatial GiST index on a.geom can be used. The btree index is not applicable. Postgres walks through the "closest" rows until it finds the first two matching your predicate. Normally, more restrictive conditions ...


2

For one particularly heavy select query, I need an index on (userid,name,valueclass). This is a good reason to add an index. It would also be good to examine your query plan. BTW, underscores between words make variables more legible. Are there any good reasons why I should NOT just add a column to the unique index I already have? A unique ...


2

So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full You are correct, but this is actually caused by the fill factor value. You cannot say that now fill factor is not 100 (assuming your case) that would be an incorrect statement because fill factor was 100 this page split event was forced. Suppose you ...


2

Yup the view only has statistics since the last reboot. To help mitigate that I setup a job that ran a query like the one you posted monthly in the morning before our maintenance window started to capture the information every month before the server rebooted. That allowed me to go back further and look at trends over time. I also had a second query that ran ...


1

createIndex := 'CREATE INDEX idx_' || Fulltable_name || '_properties_crosspr_gist ON public.' || Fulltable_name || ' USING gist (properties) WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore AND properties @> $$"block_level_1"=>"head"$$::hstore AND properties @> ...


1

Personally, I would prefer to create the index on a column which we frequently use in the where clause. Also, you might be aware that index based scans will be beneficial in cases where the query is going to fetch less than 5% of the total amount of data in a particular table. Creating an index on all the columns in a table would not be beneficial, as it ...


1

Query 1: INDEX(person_type, person_id) -- in that order INDEX(person_type, person_id, full_name) -- to be "covering" Query 2 ("covering" is not practical because of *): INDEX(person_type, full_name) -- in that order Query 3: Before making suggestions here, please explain why you have a LIMIT without an ORDER BY. See also my cookbook on making ...


1

INDEX(a) is unnecessary when you also have INDEX(a,b). So get rid of these: KEY `company_id` (`company_id`), KEY `ca` (`company_id`,`account_number`), ca is a good index, but cab is better; removing ca will encourage the optimizer to use cab. How many rows are in the result set? Was the SELECT slow during one of those big DELETE + INSERT times? If so, ...


1

We can compress B*Tree indexes. This removes redundancies from concatenated indexes. We dont get compression for free. Oracle spends more time processing the compressed data while maintaining and searching the index during query execution. Compression may increase CPU time(Extra processing overhead) while reducing I/O time. so be aware of tradeoff. ...


1

Lots of good answers already. I just want to add a rule of thumb and a worst case scenario. Rule of thumb: if an index is not used frequently by SEEK operation, it can be considered "bad", and should be revised or removed. Worst scenario: a clustered index in sql server is composed of GUID (non-sequential) column, and thus frequent inserts may cause ...


1

First add those indexes to the calls table: (CallCampaignID, CallSourceMediumID, CallDate) (CallCampaignID, CallFirstSourceMediumID, CallDate) Those should make it much faster because they allow the two joins to the calls table to check substantially less rows (it is ~70k each time now, which means millions of combinations). After that check the ...


1

Composite indexes. LEFT JOIN calls last ON last.CallSourceMediumID = SourceMediumID AND last.CallCampaignID = 222 AND last.CallDate >= '2015-03-01' AND last.CallDate <= '2015-03-31' needs INDEX(CallSourceMediumID, CallCampaignID, CallDate) (The first two columns can ...



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