New answers tagged

0

Are you not getting rapid fragmentation of that clustered index? That would negatively impact insert and select. Consider a different index You are loading data daily - I assume for the day or prior day PK cellX, cellY, timeStamp That is maximum fragmentation Consider PK timeStamp, cellX, cellY And load the data sorted by that order Even if you have ...


5

I've worked with a 30+ billion row monthly partitioned table with page compression and 10 years of history. The table schema was fairly simple with a datetime2(2) clustered index and 3 non-clustered indexes on varchar columns and a couple of non-indexed columns. Storage was about 2TB and it performed reasonably well. SqlBulkCopy was used to insert about ...


2

1. f_unaccent() Seems like you are using my function as defined here: Does PostgreSQL support “accent insensitive” collations? Note the update I just made. This is better: CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text AS $func$ SELECT public.unaccent('public.unaccent', $1) -- schema-qualify function and dictionary $func$ LANGUAGE sql ...


5

They are part of a mixed extent, the diagram just didn't draw a grey box behind them, probably more to keep it from being too busy as much as anything. Data File : Page Again, I think this is just meant to simplify and not make the diagram too busy, as well as not give any sort of suggestion that pages will necessarily be linked in a certain order. Surely ...


1

How much improvement are you expecting from the SSD? It is surprising how little physical I/O actually happens on busy indexes and I don't think I've ever solved an actual performance problem with physical I/O devices. I don't know enough about PostgresSQL to comment on buffering or recoverability and the like but I will say that choosing performance over ...


2

Some of your main considerations with included columns are: Would the query plan otherwise result in a lookup? How many executions occur for the lookup operator? Is the added overhead of maintaining that extra piece of data (for change) counter productive to performance? -and remember included columns are only present in the leaf level pages of the non ...


3

Suggested indexes always tend to include as many columns as needed by queries accessing the data, in order to have covering indexes and eliminate the need for lookups. Whether this is a good thing or not, only you can tell. It highly depends on the shape of your workload. Some queries will highly benefit from covering indexes, some others will barely ...


0

Since this is a theoretical question I'm sure there will be corner cases where that is a good idea but as a general rule I wouldn't go down that path. You will, as you state, complicate your regular queries by having to join both tables and you will also complicate your inserts/updates. A well designed, properly normalized, database with proper indexing on ...


0

Check firstly if the index fits into RAM. If yes, check the table structure and the problematic quiry with DESCRIBE.


1

Do you need to handle this directly in the database? My inclination, since you're ok with slightly stale data, is to cache individual query results, rather than the table as a whole, in a layer like memcached or redis. This is a pretty standard approach in web application development. The primary downside is that it requires development effort on the ...


3

For Postgres and Oracle: create unique index unique_combinations on my_table (least(sender_id, recipient_id), greatest(sender_id, recipient_id));


1

1000 transaction per minute = 16.67 / second = 480,000 / 8 hr day 16.67 / second is not the fast. I am getting over 100 / second on just a regular active big table. Pick your PK or at least one index that you can sort the incoming data by so you have minimal fragmentation of that index. If you can hold records to insert 100 or 1000 at a time and ...


2

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods? PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a ...


6

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance ...


2

If it's just a single table, think about the idea of having a second copy of the table used just for reporting purposes. I wrote a two-part series about my solution here: Part 1 | Part 2. Essentially you have a table that represents a copy of your transactional table, but it is optimized for your reporting workload (as such, perhaps it only has a subset of ...


1

I think Daniel's answer is probably better than mine, but just to give you the basic alternatives: Transaction replication with only that table replicated, to a different server. Pros: Instant, readable data Read locks will only block the replicated server Transactional replication is read through your transaction log, with an Agent reading all ...


2

Without touching on the obvious hardware possibilities and HA solutions, I would consider building a "staging table" which is minimally indexed or even a heap, where you could offload incoming transactions with maximum performance. A scheduled/recurring process could then asynchronously move that data into the main fact table, which could have indexes that ...


3

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


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

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


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


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


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


13

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


0

For me too many indexes means that it is more expensive to insert, update, or delete rows. Because, if the indexes are of any use they need to be maintained. Find all of the indexes with columns that you either don't query, or don't query very often and drop them. Find all of the indexes where the columns are the same, but in a different order drop all but ...


0

I have tried all thing you guys suggested. Thanks for that, it was useful but did not solve my problem. Indexes suggested by jkavalik helped a little bit, but the query was still too slow (45 seconds~). The problem was that there was 2 large tables - visits and calls. And I had to join them twice in the query. It was taking long time. After few days of ...


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


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


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


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


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


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

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


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


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


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


0

Despite of the database you are using, normally you don't add thousands of fields to a table. Your second model is more 'normalized' ( and it's easy to index ), and should work better ( I'm not an expert, anyway ). My opinion is based on normal limits of databases, that are not meant to work that way, so you're going to hit some inner limit of the engine, or ...


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


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


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


0

17 minutes is not a reasonable time to wait. Try to provide an index hint: SELECT * FROM contact USE INDEX (IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS) WHERE list_id = '014c7cba-c124-11e5-b4ea-0a4287b2e8c5' AND subscribed = 1 AND deleted = 0


0

It will do a full table scan. The columns in an index are ordered. Think about finding a person in a list of people where the list is sorted by last name, but all you know is the first name. PRIMARY KEY(b,a) would be somewhat good for WHERE b=1 AND c=2 -- it could at least narrow it down to all the rows where b=1. That is, it would do a "range scan" of ...



Top 50 recent answers are included