New answers tagged

0

It depends on the activity on the table. It there is no activity then there should be no difference.


0

I don't have direct experience of an index with this kind of REGEX so I would avoid it by creating a 'standardised' telephone number column. Create a process to go through the telephone numbers and store the clean, formatted version in the new column. Perform the 'standardising' process on the user input and use that as a search against the new telephone ...


6

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always ...


2

Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan. Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used. If so, you can build your script based upon something like below. You can put this in a stored procedure ...


1

One would hope that the indexes were created for a good reason and not just to annoy other people who would use the database. Assuming these indexes have (or at least had) a good reason to exist, writing a script to blow them away without understanding why they were created or having a plan to replace them seems like a bad idea. Potentially a better ...


0

In your example, the maintenance_work_mem you have set of 256MB should be seen by the subsequent CREATE INDEX CONCURRENTLY command, because you have changed this GUC inside your session. In fact, the docs suggest bumping up maintenance_work_mem (as you showed) for just such a purpose.


4

Without further info, this is more of speculation but judging on what we have: a table that is quite wide (1.3 to 4.0 rows per page on average) the query that is slow is using: only PWFID on the join condition, two columns Title, SITime on the select list and no other column anywhere (WHERE, HAVING etc.) Then a covering non-clustered index on ...


2

There are a few problems here: First, as jkavalik says in the comments on the OP, the order of columns in an index matters. Basically, in your case for index_rqcd to be used for filtering on rq_date, t_id has to be used before it can "see" and filter on rq_date. Since usually only one range scan on an index can be done for a query and it has to be the ...


2

On SQL Server Std Ed, 2005-2014, there is no way to get est time remaining (and Sporri's answer on this thread is simply incorrect, sorry). This thread has half an answer: SQL Server: How to track progress of CREATE INDEX command?


3

Referring to MS documentation here. Also Creating a 60GB Index on SQLFool. What I believe is happening is that when the index is being built with SORT_IN_TEMDB=OFF, the entire index build processed is logged via the DB's transaction log (DB is set to full recovery). (In addition, I expect there to be a high number of page splits due to the scattergun-nature ...


-1

Another option is to use 4 INTEGER or 2 BIGINT columns.


3

An index is added in PostgreSQL, too, when a PRIMARY KEY or a UNIQUE constraint is created, as it is clearly stated in the docs. See CREATE TABLE: PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. ... ...


2

Surely READ UNCOMMITED is a recipe for disaster? It's not called "Dirty Read" for nothing! Everything I've ever read about Oracle says so. In particular, Tom Kyte's "Oracle Database Architecture" which flat out states that Oracle refuses to provide that level and that is a good thing! I saw the MySQL tag on this post, but this is universally applicable to ...


0

INSERTs have lower impact in affected indexes than UPDATEs, however INSERTs affect all indexes (except for conditional indexes that do not meet the condition). Indexes are necessary if you need to retrieve data often and quickly. You must weigh the impact of one (indexes in updates/inserts) or the other (full scan searches): you can't have it both ways! An ...


0

The SQL Server DB Storage Engine access the data on the hard drive the same way. It can be the exact same physical file. This is why although it's bad practice, you can have just 1 physical .mdf file and have all of your indexed, non indexed data on a clustered index, and heap in the same physical file. Of course performance and disk space usage will be ...


2

You have a lot of indexes. I doubt you need all of them. Check whether all of them are in use. Instructions in the manual, chapter Examining Index Usage. If your system is configured to gather statistics, it will be particularly revealing to study: SELECT * FROM pg_stat_user_indexes These statistics are also displayed in pgAdmin. Some indexes are ...


0

in cited article: the way to force a specific subdocument to match is to use “$elemMatch”: db.generic.findOne({"props": { $elemMatch: {n: "prop1", v: 0} }}) remove "_" from field_2 - looks like a typo


1

The only reason for a recheck to happen in this particular query is that the bitmap is too large to fit in work_mem and so has to be down-graded to lossy. So to avoid the recheck, try to increase work_mem, if you can afford to. You shouldn't have to increase it by much to hold 1138854 tuples. Newer versions of PostgreSQL make this clearer, by including ...


0

The two analyses differ in the text comparison, the first shows ~~*, i.e. case insensitive (ILIKE) whilst the second shows only ~~, i.e. case sensitive (check if you didn't run LIKE by mistake the second time).


3

Add the second predicate of your query to the partial index as well: WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in ...


4

Please note that a computer generating a list of "missing indexes" should not be swallowed whole. You will still need to decide which indexes to create, which recommended indexes are near duplicates of existing indexes, and how you should want to handle those issues. It still requires you making a decision since the generated recommendations need some ...


7

Check the View Tuning Output: If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu. As always review and test the recommendations before blindly applying them to your PROD environment. I would highly suggest to look ...


2

Just remove pg_size_pretty from the query: SELECT relid::regclass AS table, indexrelid::regclass AS index, pg_relation_size(indexrelid::regclass) AS index_size,


3

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. string ... 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per ...


10

I want to know how the query execution works here The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example). After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator ...


8

The optimizer has a choice between two main strategies: Scan the table (the clustered index) checking every row to see if LoanNum = 2712. Scan & Lookup Scan the nonclustered index to find rows where LoanNum = 2712 Look up the column data for the matched rows not covered by the nonclustered index. The key point is that the nonclustered index is ...


1

Scanning are performed by loading index page by page. The number of rows fit in one page depends on the size of the index. Scanning using non clustered index will be faster compared to the clustered one, since more records will be available to be scanned per page. HTH


0

A clustered index contains all data in the row. It is not possible to tell exactly why the index was chosen without the actual plan file. I can say that based in my experience the optimizer is looking at the statistics. Because you are only returning one row then reading the smaller index and seeking to the data is much faster. You could test this by ...


0

The only query this would really benefit would be counting the male / female ratio. examples of queries your trying to run would be useful to give examples of what would help. the issue you have is an index that is basically a bit is that you put half of your data in one half, and half in the other half, there isn't actually any organisation to that index, ...


0

The optimizer evaluates the possible gains of using indexes vs doing a full scan (filtering unwanted rows on the fly). As the ratio of filtered and total rows gets closer to 1 the benefit of using index decreases. The exact tipping point is dependent on the actual data, query, etc. so it's hard to say an exact number when it becomes useless. Generally ...


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


1

Seems like a decent approach. Of course, one should apply some human verification to this before automatically dropping everything that seems unused. For example, it's conceivable that the statistics were recently reset and/or an index is only used for some occasional batch tasks.


2

pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname. So you need to use select * from pg_indexes where tablename ='asignacion' and schemaname = 'distribucion';


1

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


8

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged). In this case if you were to create the temp table with an unnamed primary key ...


1

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row. b) sequential vs ...


0

In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the tree is a fixed size, removing non-key data from internal nodes frees space for more keys to be held. All operators can be made to work with the BTree/ B+Tree ...


2

With a Cassandra index (i.e. a "secondary index", as opposed to primary keys), each node has to query its own local data for responding to a query (see the Cassandra secondary indexexes FAQ). These index are also built using a background process. This backgrounding means that the index may return false negatives in terms of hits (or false positives in ...


1

Indexing can have several purposes: - Access your data faster and to accelerate the execution time of your queries - Define the degree of uniqueness of a given column: Should every field be unique? Are duplicates allowed? When you send a request to your MySQL server, it is first assigned to the "parser" SQL which aims to verify the syntax of your request is ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


0

To check if an index is working, use SELECT COUNT(*) rather than SELECT *, otherwise you're not only measuring index performance but also the time it takes to transfer all the data across your client application, which in this case is a whooping 50,000 rows! You can significantly simplify your query and index by date datatype instead of timestamp, which is ...



Top 50 recent answers are included