Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

adding a condition kills select execution [CORE68] #392

Closed
firebird-automations opened this issue Feb 28, 2006 · 4 comments
Closed

adding a condition kills select execution [CORE68] #392

firebird-automations opened this issue Feb 28, 2006 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: centros (centros)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Alice F. Bird (firebirds)

description: 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

=>

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10092 ] => Firebird [ 14254 ]

@dyemanov
Copy link
Member

Outdated / not reproducible.

@dyemanov dyemanov closed this as not planned Won't fix, can't repro, duplicate, stale Jan 14, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants