Execution time seems not very optimal for this type of query
------------------------------------------------------------
Key: CORE-4103
URL: http://tracker.firebirdsql.org/browse/CORE-4103
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.2 Update 1
Environment: windows 7 64bit
Reporter: Dehorter Olivier
Proposed to send to bug tracker by Ph Makowski (original post at
http://www.developpez.net/forums/d1345499/sql/pourquoi-ce-plan-execution/#post7300615)
Maybe a bug ? i don't know
Fact :
The execution plan proposed by the optimizer seems not to be optimize
2 tables :
CREATE GENERATOR N_BAGUAGE_ID_DATA_GEN_NEW;
CREATE TABLE N_DATA (
ID_DATA INTEGER NOT NULL,
ID_BIRD INTEGER NOT NULL,
ID_TAXON INTEGER,
ID_SESSION INTEGER NOT NULL,
TYPE_ACTION INTEGER NOT NULL
);
ALTER TABLE N_DATA ADD PRIMARY KEY (ID_DATA);
ALTER TABLE N_DATA ADD CONSTRAINT FK_BAGUE_ID_SESSION FOREIGN KEY (ID_SESSION)
REFERENCES N_SESSION (ID_SESSION) ON DELETE CASCADE ON UPDATE CASCADE;
-- Of course some others exists
CREATE GENERATOR GEN_N_SESSION_ID;
CREATE TABLE N_SESSION (
ID_SESSION INTEGER NOT NULL,
DATE_SESSION DATE NOT NULL,
ID_LIEUDIT INTEGER
);
ALTER TABLE N_SESSION ADD CONSTRAINT PK_N_SESSION PRIMARY KEY (ID_SESSION);
CREATE INDEX IDX_N_SESSION_ANNEE ON N_SESSION COMPUTED BY (EXTRACT(YEAR FROM
N_session.Date_session));
CREATE INDEX IDX_N_SESSION_DATE ON N_SESSION (DATE_SESSION);
CREATE DESCENDING INDEX IDX_N_SESSION_DATE_DESC ON N_SESSION (DATE_SESSION);
CREATE INDEX IDX_N_SESSION_MOIS ON N_SESSION COMPUTED BY (EXTRACT(MONTH FROM
N_session.Date_session));
-- Of course some others exists
the selectivity of the interresting index is :
IDX_N_SESSION_ANNEE = 0.006
IDX_N_SESSION_MOIS = 0.08
FK_N_DATA_TAXON = 0.008
-----------------------
1st Query :
SELECT N_Data.Type_Action
FROM N_Data
INNER JOIN N_Session ON (N_Session.Id_Session =
N_Data.Id_Session)
WHERE N_Data.Id_Taxon = :Id_Taxon
Execution plan from the optimizer :
PLAN JOIN (N_DATA INDEX (FK_N_DATA_TAXON), N_SESSION INDEX (PK_N_SESSION))
Execute time = 0.5s
---------------------------------------------------------
2nd Query :
SELECT N_Data.Type_Action
FROM N_Data
INNER JOIN N_Session ON (N_Session.Id_Session =
N_Data.Id_Session AND N_Session.Actif = 1)
WHERE N_Data.Id_Taxon = :Id_Taxon
AND EXTRACT(YEAR FROM N_Session.Date_Session)
BETWEEN 1900 AND 2013
AND EXTRACT(MONTH FROM N_Session.Date_Session) IN
(1,3,5,7,9,11)
Execution plan from the optimizer :
PLAN JOIN (N_SESSION INDEX (IDX_N_SESSION_ANNEE, IDX_N_SESSION_MOIS,
IDX_N_SESSION_MOIS, IDX_N_SESSION_MOIS, IDX_N_SESSION_MOIS, IDX_N_SESSION_MOIS,
IDX_N_SESSION_MOIS), N_DATA INDEX (FK_BAGUE_ID_SESSION, FK_N_DATA_TAXON))
Execute time = 10s !!! quite normal with this plan according to selectivity
------------------------------------------------------------------
3rd Query
SELECT N_Data.Type_Action
FROM N_Data
INNER JOIN N_Session ON (N_Session.Id_Session =
N_Data.Id_Session AND N_Session.Actif = 1)
WHERE N_Data.Id_Taxon = :Id_Taxon
AND EXTRACT(YEAR FROM N_Session.Date_Session)
BETWEEN 1900 AND 2013
AND EXTRACT(MONTH FROM N_Session.Date_Session) IN
(1,3,5,7,9,11)
PLAN SORT (JOIN (N_DATA INDEX (FK_N_DATA_TAXON), N_SESSION INDEX (PK_N_SESSION,
IDX_N_SESSION_ANNEE, IDX_N_SESSION_MOIS)))
Final Execution plan from the optimizer :
PLAN JOIN (N_DATA INDEX (FK_N_DATA_TAXON), N_SESSION INDEX (PK_N_SESSION))
Execute time = 0.5s
It Seems that the plan proposed by optimizer into the second query is not the
best :(
regards
olivier
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_may
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
