Tag Info

New answers tagged

9

It seems to ignore any index I put on it Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint. Without the hint, the query optimizer (in Enterprise ...


0

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan. Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index. You may find as you create ...


3

Are there exceptions to the above clustered index rules? The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'. Your scenario is somewhat "special" in that you have a very wide table, with a ...


1

work_mem Obviously, the sort operation spills to disk: Sort Method: external merge Disk: 36224kB More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on ...


1

One of the easiest and most effective ways to increase performance on this sort of query is to execute SET work_mem=40MB (because you have ~32MB of temp file for sorting, and a little extra often helps) then run your query, and see if the EXPLAIN ANALYZE plan changes from disk to an in-memory sort. Afterwards, run RESET work_mem to put the value back to the ...


1

Depending on how selective the combined predicates are, I would imagine a good index for this particular query would be: CREATE INDEX index_name ON products (above_revenue_average ASC, start_date DESC) WHERE status > 100 AND category_id <> 5; The SELECT * is potentially problematic because the index above does not contain all columns. ...


2

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered? If you're asking if the default for a primary key on an identity column (in particular) ought to be nonclustered, I would say no. Most tables benefit from having a clustered index, so making clustered the default for a primary ...


-2

A couple of points to consider. While an index (clustered or not) on a monotonously increasing value saves you page splits during mass inserts, it creates a new hot spot at the tail end of the index. Though it may not be a problem with a single thread bulk insert, it will definitely increase contention for a multithreaded application inserting new tuples at ...


4

Actually, you do not need a Clustered Index nor a Primary Key to be created, since Unique Indexes and Non-Unique Indexes can handle the work. SQL Server has supported a Clustered Index since at least version 1.1, but the Primary Key was just a "concept" that programmers enforced by defining a unique index. But it seems that both Primary Keys and Clustered ...


0

I would start by adding a clustered index to the table. Since the primary key is unique I would just update that from nonclustered to clustered. To do that you will have to drop and recreate the constraint so if you have foreign keys defined you will need to drop those constraints first then put them back once you have the primary key put back on. A few ...


3

About: sys.dm_db_index_operational_stats The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or ...


7

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by ...


8

Ah! It's the image column. [column11] [image] NULL, Online only works on tables without blobs. Guidelines for Performing Online Index Operations


2

In addition to Craig's thorough answer, I wanted to add that the cover of the book you reference says: Covers Oracle, DB2 & SQL Server So I wouldn't trust it to be a great source of advice on PostgreSQL in particular. Every RDBMS can be surprisingly different! I'm a little confused about your original question, but here's an example showing that ...


-1

You haven't posted your query or example data. But the most common reason indexes are not used has to do with volume. Indexes are like a phonebook that translates a column to a row location. If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table. But for more than a few rows, ...


8

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either. If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index. I can't figure out what you're trying to say with: If ...



Top 50 recent answers are included