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

Jump to bottom

Query optimizer in 2.5.8 and 3.x is not using importan index (but early 2.5 version uses it) [CORE6070] #6320

Open
firebird-automations opened this issue May 29, 2019 · 5 comments
Open

Query optimizer in 2.5.8 and 3.x is not using importan index (but early 2.5 version uses it) [CORE6070] #6320

firebird-automations opened this issue May 29, 2019 · 5 comments
Labels
affect-version: 2.5.8 affect-version: 3.0.3 affect-version: 3.0.4 priority: major type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Daniel (danyschaer)

Hi;

On my database one table is this:

CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL COLLATE ES_ES_AI,
PROC Char(10) NOT NULL COLLATE ES_ES_AI,
MIEM Char(10) NOT NULL COLLATE ES_ES_AI,
TIPO Char(1) NOT NULL COLLATE ES_ES_AI,
AVIS Char(3) NOT NULL COLLATE ES_ES_AI,
HECH Char(1) NOT NULL COLLATE ES_ES_AI,
FECH Char(8) NOT NULL COLLATE ES_ES_AI,
HORA Char(4) NOT NULL COLLATE ES_ES_AI,
DSCR Varchar(200) NOT NULL COLLATE ES_ES_AI,
FOJA Varchar(10) NOT NULL COLLATE ES_ES_AI,
PRES Char(1) NOT NULL COLLATE ES_ES_AI,
REPI Char(1) NOT NULL COLLATE ES_ES_AI,
OWNR Char(10) NOT NULL COLLATE ES_ES_AI,
TEXT Blob sub_type 0,
"EDIT" Char(23) NOT NULL COLLATE ES_ES_AI,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);
CREATE INDEX MOVI_FECH ON MOVI (FECH,HORA);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_OWNR ON MOVI (OWNR);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_REPI ON MOVI (REPI);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);

The database has more than one millon records in this table and 46.000 records in table PROC. I have to do this query:

select
"PROC"."PROC", "PROC"."CARP", "PROC"."ACTO", "PROC"."DEMA", "PROC"."OBSE", "TPRO"."DSCR" as "D_TPRO",
"PROC"."OJUD", "PROC"."INST", "PROC"."EXP1", "PROC"."EXP2", "PROC"."EXP3", "PROC"."EXP4"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "OJUD" O1 on "PROC"."OJUD" = O1."OJUD"
left outer join "ZONA" O2 on O1."ZONA" = O2."ZONA"
where
(
(
("PROC"."PROC" in (
select distinct "PROC"
from "MOVI"
where
"MOVI"."TIPO" in ('1', 'A', 'B')
and ("MOVI"."FECH" between '20190301' and '20190412')
and "MOVI"."MIEM" = '15JMS45D7A'
and "MOVI"."HECH" = 'N'
order by "PROC"
)
)
)
)
order by O2."ORDE", O1."ORDE", lower("PROC"."ACTO"), lower("PROC"."DEMA")

On early 2.5.x versions, this query need up to 1 minute in the test machine to finish, but the same machine 2.5.8 and 3.x are using more than 20 minutes.

Under 2.5.0 the query optimizer builds a plan using MOVI_FECH:

PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))

But under 2.5.8 the query optimizer does not use MOVI_FECH

PLAN SORT ((MOVI INDEX (MOVI_PROC)))

I found that 2.5.8 only uses MOVI_FECH if I do ("MOVI"."FECH" = '20190301') but it does not use that index if I use between or if I use >= and/or <=.

I thing this is the problem that makes my query to need 20 minutes instead of 1 minute.

Thank you.
@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Reformatted the original query for readiblity

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Hi;

On my database one table is this:

CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL COLLATE ES_ES_AI,
PROC Char(10) NOT NULL COLLATE ES_ES_AI,
MIEM Char(10) NOT NULL COLLATE ES_ES_AI,
TIPO Char(1) NOT NULL COLLATE ES_ES_AI,
AVIS Char(3) NOT NULL COLLATE ES_ES_AI,
HECH Char(1) NOT NULL COLLATE ES_ES_AI,
FECH Char(8) NOT NULL COLLATE ES_ES_AI,
HORA Char(4) NOT NULL COLLATE ES_ES_AI,
DSCR Varchar(200) NOT NULL COLLATE ES_ES_AI,
FOJA Varchar(10) NOT NULL COLLATE ES_ES_AI,
PRES Char(1) NOT NULL COLLATE ES_ES_AI,
REPI Char(1) NOT NULL COLLATE ES_ES_AI,
OWNR Char(10) NOT NULL COLLATE ES_ES_AI,
TEXT Blob sub_type 0,
"EDIT" Char(23) NOT NULL COLLATE ES_ES_AI,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);
CREATE INDEX MOVI_FECH ON MOVI (FECH,HORA);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_OWNR ON MOVI (OWNR);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_REPI ON MOVI (REPI);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);

The database has more than one millon records in this table and 46.000 records in table PROC. I have to do this query:

select "PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR" as "D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4" from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA" o2 on o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from "MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI"."FECH" between '20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" = 'N' order by "PROC")))) order by o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")

On early 2.5.x versions, this query need up to 1 minute in the test machine to finish, but the same machine 2.5.8 and 3.x are using more than 20 minutes.

Under 2.5.0 the query optimizer builds a plan using MOVI_FECH:

PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))

But under 2.5.8 the query optimizer does not use MOVI_FECH

PLAN SORT ((MOVI INDEX (MOVI_PROC)))

I found that 2.5.8 only uses MOVI_FECH if I do ("MOVI"."FECH" = '20190301') but it does not use that index if I use between or if I use >= and/or <=.

I thing this is the problem that makes my query to need 20 minutes instead of 1 minute.

Thank you.

=>

Hi;

On my database one table is this:

CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL COLLATE ES_ES_AI,
PROC Char(10) NOT NULL COLLATE ES_ES_AI,
MIEM Char(10) NOT NULL COLLATE ES_ES_AI,
TIPO Char(1) NOT NULL COLLATE ES_ES_AI,
AVIS Char(3) NOT NULL COLLATE ES_ES_AI,
HECH Char(1) NOT NULL COLLATE ES_ES_AI,
FECH Char(8) NOT NULL COLLATE ES_ES_AI,
HORA Char(4) NOT NULL COLLATE ES_ES_AI,
DSCR Varchar(200) NOT NULL COLLATE ES_ES_AI,
FOJA Varchar(10) NOT NULL COLLATE ES_ES_AI,
PRES Char(1) NOT NULL COLLATE ES_ES_AI,
REPI Char(1) NOT NULL COLLATE ES_ES_AI,
OWNR Char(10) NOT NULL COLLATE ES_ES_AI,
TEXT Blob sub_type 0,
"EDIT" Char(23) NOT NULL COLLATE ES_ES_AI,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);
CREATE INDEX MOVI_FECH ON MOVI (FECH,HORA);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_OWNR ON MOVI (OWNR);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_REPI ON MOVI (REPI);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);

The database has more than one millon records in this table and 46.000 records in table PROC. I have to do this query:

select
"PROC"."PROC", "PROC"."CARP", "PROC"."ACTO", "PROC"."DEMA", "PROC"."OBSE", "TPRO"."DSCR" as "D_TPRO",
"PROC"."OJUD", "PROC"."INST", "PROC"."EXP1", "PROC"."EXP2", "PROC"."EXP3", "PROC"."EXP4"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "OJUD" O1 on "PROC"."OJUD" = O1."OJUD"
left outer join "ZONA" O2 on O1."ZONA" = O2."ZONA"
where
(
(
("PROC"."PROC" in (
select distinct "PROC"
from "MOVI"
where
"MOVI"."TIPO" in ('1', 'A', 'B')
and ("MOVI"."FECH" between '20190301' and '20190412')
and "MOVI"."MIEM" = '15JMS45D7A'
and "MOVI"."HECH" = 'N'
order by "PROC"
)
)
)
)
order by O2."ORDE", O1."ORDE", lower("PROC"."ACTO"), lower("PROC"."DEMA")

On early 2.5.x versions, this query need up to 1 minute in the test machine to finish, but the same machine 2.5.8 and 3.x are using more than 20 minutes.

Under 2.5.0 the query optimizer builds a plan using MOVI_FECH:

PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))

But under 2.5.8 the query optimizer does not use MOVI_FECH

PLAN SORT ((MOVI INDEX (MOVI_PROC)))

I found that 2.5.8 only uses MOVI_FECH if I do ("MOVI"."FECH" = '20190301') but it does not use that index if I use between or if I use >= and/or <=.

I thing this is the problem that makes my query to need 20 minutes instead of 1 minute.

Thank you.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

This is a support question which should have been posted to the Firebird support mailing list, not posted to this tracker.

The use of
("PROC"."PROC" ***in (select ...***
is specifically discouraged in Firebird (all versions).

The optimal syntax would be:

  EXISTS\(
    SELECT 1
    FROM "MOVI"
    WHERE
      "MOVI"\."TIPO" IN \('1', 'A', 'B'\)
      AND \("MOVI"\."FECH" BETWEEN '20190301' AND '20190412'\)
      AND "MOVI"\."MIEM" = '15JMS45D7A'
      AND "MOVI"\."HECH" = 'N'
      AND "MOVI"\."PROC" = "PROC"\."PROC"
  \)

Please try this syntax and advise.

Further, depending on the manner in which you normally would query "MOVI" rows, you should consider creating a new compound index on the table combining "PROC", "MIEM", "FECH" (with the fields ordered from least to most specific/selective). That would also improve the performance of the query

@firebird-automations
Copy link
Collaborator Author

Commented by: Daniel (danyschaer)

Before asking here, I already was testing your suggestion:

select
"PROC"."PROC", "PROC"."CARP", "PROC"."ACTO", "PROC"."DEMA", "PROC"."OBSE", "TPRO"."DSCR" as "D_TPRO",
"PROC"."OJUD", "PROC"."INST", "PROC"."EXP1", "PROC"."EXP2", "PROC"."EXP3", "PROC"."EXP4"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "OJUD" O1 on "PROC"."OJUD" = O1."OJUD"
left outer join "ZONA" O2 on O1."ZONA" = O2."ZONA"
where
(
(
( EXISTS(
SELECT 1
FROM "MOVI"
WHERE
"MOVI"."TIPO" IN ('1', 'A', 'B')
AND ("MOVI"."FECH" BETWEEN '20190301' AND '20190412')
AND "MOVI"."MIEM" = '15JMS45D7A'
AND "MOVI"."HECH" = 'N'
AND "MOVI"."PROC" = "PROC"."PROC"
)
)
)
)
order by O2."ORDE", O1."ORDE", lower("PROC"."ACTO"), lower("PROC"."DEMA")

This sintaxys DOES NOT make any diference in performance. That means, it is very fast in early 2.5 but it is the same slow under 2.5.8. Here you have the resulting plan under 2.5.8:

Preparing query: select
"PROC"."PROC", "PROC"."CARP", "PROC"."ACTO", "PROC"."DEMA", "PROC"."OBSE", "TPRO"."DSCR" as "D_TPRO",
"PROC"."OJUD", "PROC"."INST", "PROC"."EXP1", "PROC"."EXP2", "PROC"."EXP3", "PROC"."EXP4"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "OJUD" O1 on "PROC"."OJUD" = O1."OJUD"
left outer join "ZONA" O2 on O1."ZONA" = O2."ZONA"
where
(
(
( EXISTS(
SELECT 1
FROM "MOVI"
WHERE
"MOVI"."TIPO" IN ('1', 'A', 'B')
AND ("MOVI"."FECH" BETWEEN '20190301' AND '20190412')
AND "MOVI"."MIEM" = '15JMS45D7A'
AND "MOVI"."HECH" = 'N'
AND "MOVI"."PROC" = "PROC"."PROC"
)
)
)
)
order by O2."ORDE", O1."ORDE", lower("PROC"."ACTO"), lower("PROC"."DEMA")
Prepare time: 0.000s
Field #⁠01: PROC.PROC Alias:PROC Type:STRING(10)
Field #⁠02: PROC.CARP Alias:CARP Type:STRING(18)
Field #⁠03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #⁠04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #⁠05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #⁠06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
Field #⁠07: PROC.OJUD Alias:OJUD Type:STRING(10)
Field #⁠08: PROC.INST Alias:INST Type:STRING(1)
Field #⁠09: PROC.EXP1 Alias:EXP1 Type:STRING(18)
Field #⁠10: PROC.EXP2 Alias:EXP2 Type:STRING(18)
Field #⁠11: PROC.EXP3 Alias:EXP3 Type:STRING(18)
Field #⁠12: PROC.EXP4 Alias:EXP4 Type:STRING(18)
PLAN (MOVI INDEX (MOVI_PROC))
PLAN SORT (JOIN (JOIN (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)), O1 INDEX (OJUD_PK)), O2 INDEX (ZONA_PK)))
Script execution finished.

It is absolutely clear it is a problem, and not a "support question". 20 minutes ...

@firebird-automations
Copy link
Collaborator Author

Commented by: Daniel (danyschaer)

I found where is the problem. This is not about using "IN". The thing is very simple: From Firebird 2.5.0 to Firebird 2.5.3 the query optimizer uses the compound index MOVI_FECH (FECH,HORA) ; but from Firebird 2.5.4 the query optimizer does not use this compound index anymore. If under 2.5.8 I add now a new index for FECH, then the query optimizer uses it, and the query is faster again.

Why the optimizer does not use the compound index MOVI_FECH after Firebird 2.5.3 ??

And please!!! note that if with FB 2.5.8 I only do the MOVI part of the query, then the optimizer uses MOVI_FECH:

Starting transaction...
Preparing query: select distinct "PROC"
from "MOVI"
where
"MOVI"."TIPO" in ('1', 'A', 'B')
and ("MOVI"."FECH" between '20190301' and '20190412')
and "MOVI"."MIEM" = '15JMS45D7A'
and "MOVI"."HECH" = 'N'
Prepare time: 0.015s
Field #⁠01: MOVI.PROC Alias:PROC Type:STRING(10)
PLAN SORT ((MOVI INDEX (MOVI_FECH, MOVI_MIEM)))
Script execution finished.

Which means that MOVI_FECH is usefull. And it also uses MOVI_MIEM !!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.5.8 affect-version: 3.0.3 affect-version: 3.0.4 priority: major type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations