Hot answers tagged

16

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


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


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


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


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


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


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


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


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


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


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 an actual answer to this question: SQL Server: How to track progress of CREATE INDEX command?


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';


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,


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


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


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

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

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


1

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.


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


1

(Assuming InnoDB...) The data and the PRIMARY KEY are in one BTree. Each secondary INDEX (including UNIQUE indexes) is in its separate BTree. An update requires modifying a record in the data BTree. If that also involves updating a column that is in any index it requires, effectively, a DELETE from that BTree plus an INSERT somewhere else in that same ...


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


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


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.


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



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