Search Results

If clustering is not supported, or you want the one clustered index on the table for something else, then again you'd need to use a wide covering index or an index that INCLUDEs all the other columns. … table index order with the CLUSTER command …

index is the only solution. … Perhaps go for an online index build if possible - that will take longer but won't completely block part of the application while it does the job. …

to use a combined index if present. … If you only have an index on each individually but no combined index the DB will either search one or the other index and separately filter the results with the second clause, or scan both and marry the …

Index creation is essentially a sort operation, so is at best has a growth complexity of the order n log n on average (you might find it does better in some cases, and is not likely to do much worse). … If all your relevant data pages fit into RAM and are already in RAM, and the index will fit also, and your DBMS does not force index pages to be written before the creation is complete (so index blocks …

There will be no difference for read operations, once written the entries in the index will be the same. … Only create a unique index for values that you know should be unique. …

Postgres supports indexes on expressions so if the value you want to index can be computed from other values in the row this will work (assuming the queries you are using a written in a way that the query … planner can see the use for the index). …

You are right in that your example query would not use that index. … If you do define [customerId], [dateCreated] as an index, for example, remember that the query planner will be able to use that for queries that would use an index on just [customerId] if present. …

With an index on Sales.UID it will likely scan users and be able to join using sales more efficiently because the joining field is pre-sorted by the index. … If you are likely to search by date rather than listing all sales, then an index with Sales.Date as the first covered column might be more helpful. …

Would creating an nonclustered index with the same column(s) as the clustered index be the most efficient? … What does the optimizer do in this case - does it still make a plan to include these "bad" indexes that just reference the clustered index, or will it know to ignore the with(index) request? …

Essentially you would be manually partitioning the table (and appying the index to only one of the partitions). … You could maintain an extra column with an index and only have a value in that column when the condition you want the index to be based upon is true, but this is likely to be labour intensive and of limited …

heap or clustered index) once a row matches all the predicates that the index could satisfy. … by the index. …

I asume by "recalculating" you are referring to completely rebuilding an index. … You can reduce the affect of index rebuilds if you have Enterprise Edition by using online index builds - there will still be a time were the table is locked as the newly generated index is attached to …

I think the problem stopping an index on time being used for filtering is the use of a function with a reference to the row: COALESCE(?, time). …

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value. … If it is index scanning anyway you might find it faster to do that and then filter out the two you are not looking for results for. …

If laurenz's suggestion of enforcing limitations is not possible for your product, because users will kick back or other stake-holders within the client (as you are not likely talking directly to the …