Tag Info

Hot answers tagged

11

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error. Your query becomes: SELECT count(*) ...


10

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


9

Your Predicate is different to your Seek Predicate. A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done. But before the data is returned, it checks every row ...


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


8

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


8

Should I mark a composite index as unique if it already contains the primary key? Probably not. The optimizer can generally use information about the uniqueness of the contained key column anyway, so there's no real advantage. There is also an important consequence of marking an index unique on update plans that modify keys of that index to consider: ...


6

Answer to question 1: From what you posted you can drop the first two indexes as the third will cover all of the queries you mention and the query optimizer see that as well when it builds the query plan (based on the plan you posted.) Answer to question 2: It's always using the third index because it has more data already in the index with the two ...


6

SQL knows it's unique already (if it includes the PK, it can't get any more unique), regardless of whether you explicitly tell it. The big difference between a non-unique index and a unique index is that non-unique indexes require the clustered index key (with uniquifier value if the CIX is not declared as unique) at the higher levels of the index, not just ...


5

Short version: no. There's no practical way (in PostgreSQL, at least) to index a pattern column so it can be matched against plaintext inputs in a way that will speed up "does this plaintext match any of these patterns" queries. PostgreSQL would need a special custom index type that "understood" pattern matches. I'm not sure how practical it'd be to ...


4

Your existing index on DATE is obviously useless for the query. The first obvious step for your query: SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2'; is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like: CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b); ...


4

"date" Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like: CREATE TABLE test ( id serial PRIMARY KEY , ts timestamp NOT NULL -- also adding NOT NULL ...


4

Masi, The PostgreSQL B-Tree index is very strongly based on the implementation by Lehman and Yao, which includes a lot of work oriented around multi-version concurrency control, but there's still great info in this paper. Of course, PostgreSQL doesn't make a 100% accurate replica of the method in the paper, and to find the differences, there will be almost ...


3

Not redundant, but rarely practical. An INDEX has limitations, based on 255 characters. (There are too many wrinkles for me to go into detail.) FULLTEXT is often used for big blocks of text. Would you be looking at 'words' and doing = comparisons on the whole column? Seems strange. Here's one trick for when I want to search for a phrase that messes up ...


3

All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general. The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include: You have substantially fewer rows in your tables for the ...


3

There are a few things I can clarify for you here: Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before). Mysqlpump does this by default. When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation). For MySQL 5.7 ...


3

GetItemToProcessIndex is not fully seekable because your where clause is on ItemState + LastAccessTime + CreationTime. The indexed columns and the where clause are a not perfect match. If you create a covering index on ItemState + LastAccessTime + CreationTime, for each match you get from GetItemToProcessIndex, you also get the value of your Primary Key ...


2

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.


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


2

Index fragmentation is much overrated. Do not worry about it. Two adjacent, somewhat-empty, blocks are merged together by InnoDB as the natural processing. Random actions on a BTree cause it to naturally gravitate toward an average of 69% full. Sure, this is not 100%, but the overhead of "fixing" it is not worth it. SHOW TABLE STATUS gives you some ...


2

You could also embed WHERE 1=1 SELECT count(*) as c, SUBSTRING ( domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2 ) as tld FROM `domains_import` WHERE 1=1 GROUP BY tld ORDER BY c desc LIMIT 100 ypercube just asked me Rolando, is MySQL's optimizer so dumb that a simple always true condition ...


2

Not the stated question but may get better query plans with better queries You are killing the left outer with the where where profiledmo1_.Id=@P0 turns that into a join On indexes just the first two select addressdmo0_.Id as Id1_20_ , ... , addressdmo0_.Zip as Zip14_20_ from dbo.Address addressdmo0_ join dbo.Profile profiledmo1_ ...


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


2

On your indices Ok, first things first. Assuming some structure like this { _id: new ObjectId(), date: new ISODate(), message: "Hello, Multikey Indices!", tags: ["MongoDB","Indices","Multikey"] } indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all ...


2

Can you get rid of the leading % in '%uggjplove13@gmail.com%'? If so, that will make a world of difference! Any index starting with EMAIL can be used (assuming you get rid of upper), and the query will be fast. If not, read on... FULLTEXT(email), when it can be used, will be very fast. But you may have to build some intelligence into when to use it, and ...


2

If the only thing that inserts / updates data in this table is an out-of-hours process I'd be tempted to apply as many indexes as you can. 999 are allowed for a SQL Server 2012 table. You can determine how many you need using the Database Tuning Advisor. Obviously this will slow down the load process and consume a lot more disk space - both from the ...


2

Migrate piece-wise. Rename your table to tblsapdispatch_old. Create a new table called tblsapdispatch_new. This new table has the partitioning you want. Create a view called tblsapdispatch which unions the two together. This way the application is agnostic to the change. Move data from _old to _new in batches. The batch size will be found by testing. Move ...


2

I see two problems PROBLEM #1 : No WHERE clause Any query without a where clause is bound to do full scans PROBLEM #2 : There are way too few rows Over the years, I have mentioned a special rule-of-thumb: If MySQL has to read more than 5% of the rows in a table when choosing an index during Query Optimization, it will go to another index. If no index ...


2

Your added comment is on the right track already: I further found out that the inefficient index is only used if the search query (foo in my example) appears in pg_stats.most_common_vals for this column. So I assume this skews the estimated costs into the wrong direction. Any ideas how to fix this? If 'foo' is a very common, Postgres expects it ...


1

The first, most important, thing you can do is get rid of that use of json fields. If you know the field you'll be querying in advance, preferably make it a real column. At the cost of inefficiently widening the table you can do that with a generated column using a trigger, then index the generated column. It'll hurt your insert performance a bit, but you ...


1

This error code [ORA-01654] means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error you can simply add data file using ALTER TABLESPACE ADD DATAFILE ...



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