Issue Details (XML | Word | Printable)

Key: CORE-68
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: centros
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

adding a condition kills select execution

Created: 28/Feb/06 12:00 AM   Updated: 19/Jul/06 03:51 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

SF_ID: 1440232


 Description  « Hide
SFID: 1440232#
Submitted By: centros

Problem:
Adding the SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
into a select
dramatically increases the select execution time, about
200 to 300 times.

Testing:
All selects were tested with some indexes set Inactive
in first try and also with the same indexes
set Active in second try. Results are under selects
marked I -> for indexes Inactive, A -> for Active.
These indexes are: SKLPOH.Recordactive,
SKLPOH.Stornovano and SKLPOHDETAIL.Recordactive
This was made only to find out, whether that indexes
have some influence to the problem tested.
Tests were made on database sent to Dmitry last week.
Of course database was backuped and restored.
Refer to Field-test item #1432475 - INDEXES WITH LOW
SELECTIVITY KILL THE SELECT EXECUTION.

Result:
Active indexes with bad selectivity are included in a
plan, but it has no real bad influence
on select execution. The problem depends only on a
fact, whether the SKLPOHDETAIL.EIDSKLADKARTA = 3
condition is included in the select statement or not.
Problem with indexes is not in a select itself,
but in an execution of the SALDO procedure, which is
listed below, in another test case.

1.1 - Grouped select WITHOUT the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
sum(SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP)
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1'
group by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
order by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
I ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH)))
250 ms, 29.558 buffer reads
A ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH)))
328 ms, 29.483 buffer reads

1.2 - Grouped select WITH the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
sum(SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP)
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1' and
SKLPOHDETAIL.EIDSKLADKARTA = 3
group by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
order by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
I ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA)))
55.015 ms, 228.372 buffer reads
A ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA)))
54.969 ms, 228.297 buffer reads

2.1 - Ungrouped select WITHOUT the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI,SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1'
I ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH))
125 ms, 11.750 buffer reads
A ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH))
235 ms, 11.791 buffer reads

2.2 - Ungrouped select WITH the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI,SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1' and
SKLPOHDETAIL.EIDSKLADKARTA = 3
I ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA))
40.390 ms, 229.273 buffer reads
A ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA))
40.344 ms, 228.297 buffer reads

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.