Hot answers tagged

48

Solution for Postgres 9.1 CREATE INDEX idx_time_limits_inversed ON time_limits (id_phi, start_date_time, end_date_time DESC); In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. I wrote more in this closely related answer on SO....


14

You are getting the message Impossible WHERE noticed after reading const tables This is documented in the page you already linked. MySQL has read all const (and system) tables and notice that the WHERE clause is always false const tables are defined as The table has at most one matching row, which is read at the start of the query. ... ...


13

Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700? You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either. It could only be used to find the rows that "might" match a WHERE and the server layer (above the ...


9

This has a simple reason. In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits. There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.


7

You write: Each customer can have multiple sites, but only one should be displayed in this list. Yet, your query retrieves all rows. That would be a point to optimize. But you also do not define which site is to be picked. Either way, it does not matter much here. Your EXPLAIN shows only 5026 rows for the site scan (5018 for the customer scan). So ...


7

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria. Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain ...


6

PROBLEM #1 Look at the query select last_name from employees order by last_name; I don't see a meaningful WHERE clause, and neither does the MySQL Query Optimizer. There is no incentive to use an index. PROBLEM #2 Look at the query select last_name from employees force index(idx_last_name) order by last_name; You gave it an index, but the Query ...


6

Cost based optimizers all work via a variety of proprietary algorithms (or you can read them for open source databases), but they typically work by assigning a reference operation a value of 1. For example, in SQL Server an operation with a cost estimate of 1 takes 1/320th of a second on a reference computer under some developer's desk in Redmond. The ...


6

I suspect that this is not possible the way you imagine it. One important reason is that the actual run time is very hardware dependent, and many of the optimization decisions that the database engine does are effectively about balancing the use of the different hardware components (e.g., disk, memory, CPU). I suggest that you run a bunch of queries ...


6

In addition to the good advice by @Craig and @dezso: Statistics The count is 3940689. Yet, your query plan says: Seq Scan on posts (cost=0.00..205984.62 rows=**3387494** width=12) And your count is based on a selection: Rows Removed by Filter: 404218 4344907 (3940689 + 404218) >> 3387494. Your statistics are not up to date. Something may be ...


5

I don't think this is possible. If I'm not mistaken, the optimizer doesn't even know (or at least doesn't try to know) that the statement will "touch" other tables as well. It's not explicitely documented, but the following quote from the manual only talks about the tables "referenced" in the SQL statement (emphasis is mine): The row source tree is the ...


5

actual time=8163.890..8163.893 means Initializing that step ("startup") took 8163.890ms Running the whole step took 8163.893ms So in that case nearly the complete work was done in the startup phase of that step. Edit: The same logic is "applied" to the cost information cost=2928781.21..2929243.02 means: The cost to initialize this step was estimated at:...


5

This seems to be a limitation of the optimizer (or perhaps a bug). The plan for this query: SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1 union all ( SELECT * FROM test2 WHERE upper(field1)='FIELD1=1' union all SELECT * FROM test2 WHERE field3=1 ); shows that it is not the fault of bad stats, and also demonstrates a ...


5

The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index. A single index over both columns in the WHERE clause: ALTER TABLE clients ADD KEY(source,added) -- adding this ALTER TABLE clients ADD KEY(added,source) -- or this ...will usually help you more ...


5

type: index means it's an index scan. That is, it's scanning through an entire index of that table. An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index. There are as many ...


5

In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics. When you run explain, the rows column will tell you, for each table access, how many rows will be examined by using the intended filter. There are two ways to calculate that: ...


5

Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. It can do this selectively, so some blocks can be converted lossy while others not. If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information ...


4

Erwin's answer is already comprehensive, however: Range types for timestamps are available in PostgreSQL 9.1 with the Temporal extension from Jeff Davis: https://github.com/jeff-davis/PostgreSQL-Temporal Note: has limited features (uses Timestamptz, and you can only have the '[)' style overlap afaik). Also, there's lots of other great reasons to upgrade to ...


4

Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order. Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index ...


4

It's a hash join


4

It made my head hurt, but just a bit... It appears each event ID is a specific date/time, such as movies (which it appears) one event is the movie at 2:30 on Day X, another event is the movie @ 4:45 on Day X. The same movie on Day Y @ 2:30 would be a different ID... That said, you are trying to breakdown counts that are box-office specific vs web-based ...


4

MySQL currently doesn't support this (unlike nearly all other DBMS). I think this will be in 5.6 but I'm not entirely sure. I don't think there is any workaround for that (except upgrading to a DBMS that does support this)


4

Unfortunately the answers are as follows: Is there a unit for cost in an Oracle execution plan? Not really. I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? Nope. What does this number stand for? It's defined like this: (see the glossary) A numeric internal measure that ...


3

Explain plan does not tell you what is actually the most costly "operation". The "Cost" column is a guess - it is a value estimated by optimizer. So is "Cardinality" column and "Bytes" column. http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i18300 In your example, your optimizer tells you: I decide to use this plan because I guess that ...


3

I think I've figured it out. GENROW means it's creating a temporary table which is then joined to an actual table. This is usually done when the query contains an IN (...) statement. Specific to SAP, this is usually done for FOR ALL ENTRIES queries.


3

Sort Method: external merge Disk: 92048kB Throw more work_mem at the problem. Lots more. Try: SET LOCAL work_mem = '300MB'; Be aware that if you're running it in lots of concurrent connections you could exhaust system RAM. So SET only in individual sessions. Your row-count estimate on the aggregate is a bit dodgy (http://explain.depesz.com/s/RXbq) ...


3

Cleanse your data before storing it. Otherwise, INDEXes may be useless. I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless. This SELECT would run a lot faster because of the index: SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = '2948' OK, you don't like the ...


3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry): ALTER TABLE entry_log ADD INDEX card_no__date__last_entry__ix (card_no, date, last_entry) ; and then use this query: SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG FROM entry_log WHERE card_no = LPAD('2948', 32, '0') ORDER ...


3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON rtr2....


3

In this case, it's doing an Index Scan. As best as I know, the difference between an Index scan and a Bitmap Index/Heap Scan is that the former will read pages in the order defined by the index, while the latter will create a bitmap of pages to read (possibly from multiple indexes), order the results, and read them in [heap] order. Correct. There are also ...



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