The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
2answers
122 views

Can I get some rationale from the Optimzer why it chose a certain explain plan?

This is not about reading an explain plan. This is not about adding any hints or measuring performance of a specific query. I have always been wondering whether it's possible for a given explain ...
0
votes
2answers
38 views

Why search by date field requires engine to touch all records?

Why search by date field requires engine to touch all records? Is there any way to avoid it? Following query EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ; shows that ...
3
votes
0answers
70 views

Why would function based indices I’ve created lower the cost but now show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was ok to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
1
vote
2answers
337 views

Very slow execution of a simple query

I try to create a report for my data, but it is really slow on a big table. The table structure is: CREATE TABLE posts ( id serial NOT NULL, project_id integer, moderation character ...
2
votes
1answer
46 views

Understanding postgres reason for picking this query plan

I ran a query: select * from orderline where orderid = 12345 and status > 0 that gave me the following plan: Bitmap Heap Scan on orderline (cost=286370.00..531410.78 rows=76372 width=155) ...
0
votes
0answers
131 views

MySQL: how to determine key_len and its role in performance improvement of a query

I have a query, below is the structure of the table: CREATE TABLE `jos_tag_rules_fields` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `label` varchar(255) ...
1
vote
0answers
53 views

Oracle sql developer tree shaped represantation of Explain_Plan

I want to ask what exactly happens to the following Explain_Plan table : It firsts Joins the two tables Products & Order_Items then the output is joined with Orders and then with the ...
0
votes
1answer
82 views

Help interpreting a MySQL execution plan

I'm trying to understand a slow query. The SELECT has been running (at this moment) for almost 18000 seconds (output from a SELECT on PROCESSLIST): id: 22 time: 17870 ...
0
votes
2answers
210 views

Is there any manual for the visual explain in mysql? The output plan is unreadable

Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
4
votes
2answers
2k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
3
votes
1answer
477 views

Measuring the relative performance of queries given the assertion that “cost” isn't reliable

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. Source Does ...
2
votes
1answer
183 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', ...
3
votes
1answer
594 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
2
votes
1answer
935 views

Postgres multiple joins slow query, how to store default child record

Ive got a small database in Postgres, about 10,000 records storing company customers. I have a "slow" performing query (about half a second) which is performed very frequently, and my boss wants me to ...
5
votes
1answer
507 views

Execution plan using B+ tree index, but also sorts

I'm using Oracle sqlplus. And I have the following query: SELECT fooID from foo MINUS SELECT fooID from bar; I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
2
votes
2answers
1k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
5
votes
1answer
1k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
1
vote
1answer
1k views

What does TBSCAN GENROW mean in Explain Plan?

In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean? SELECT STATEMENT ( Estimated Costs = 1.811E+03 [timerons] ) 1 RETURN 2 NLJOIN 3 [O] TBSCAN 4 SORT ...
6
votes
2answers
116 views

Is there a good “rule of thumb” for translating EXPLAIN cost to (wall-clock) runtime?

From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have ...
1
vote
1answer
445 views

What does HSJOIN mean in an Explain Plan?

I have the following EXPLAIN PLAN results from a query on my DB2 database. 0 SELECT STATEMENT ( Estimated Costs = 5.928E+02 [timerons] ) 1 RETURN 2 HSJOIN 3 [O] ...