Hi

Dne 19.04.2010 9:48, Svein Erling Tysvær napsal(a):

> Which Firebird version are you using?

I'm currently using version 2.1.4.18270.

> I don't know about Firebird 2.0, but Firebird 1.5 only has information about the selectivity of the complete index, not of its individual fields. If so, I kind of think there are two possible reasons for not using the index containing both fields:
> 1) The index has so poor selectivity that NATURAL is a better choice (if so, there's nothing to improve)
>

I think that NATURAL will be never used if there is any possible index
to use doesn't care about the selectivity of that index. I think
selectivity is taken into consideration only when more than 1 index
matches needs for query.

> 2) The optimizer may not know that using an index on SRC_DIR_NAME, ID_CSYMBOL is equally good or better than an index using simply SRC_DIR_NAME (thinking about it, it is obvious, but if all you're doing is choosing between two indexes with a selectivity of 0.0000007 and 0.5, then you of course prefer the one with 0.0000007). If this is the case, you can try either:
>

And thats strange IMHO. There is an index where only a part (column
SRC_DIR_NAME) can be used and there is another index, that contains all
columns, which are used in where condition.

> a) Don't prepare until you have to (possibly even updating the statistics shortly before the prepare, I don't know whether that can help or not), hoping a better plan will be chosen when you have final data
>

That means I have to run SET STATISTIC query for this table after
previous step. And I don't like this idea. In my mind this type of query
belongs to database maintenance and not to standard client application.

> b) Add NODE_ID to IX_ZLEVELS_DIR_SYM to increase selectivity hoping that would improve the plan
>

Yes - it will probably help, but I don't want to waste disk space and
computing performace. This table will have like hundreds millions of
records. Whole database will have like 200GBs or more when it will be
filled with data

> c) Remove NODE_ID from IX_ZLEVELS_DIR_NODE hoping that would improve the plan (though this could slow down UPDATEs and DELETEs)
>

This will slow down performace of some other steps significantly so I
will not go for it.

> Other things you should consider, is the selectivity of IX_STREETS_LINK_ALT and FK_STREETS_CSYMBOL, if FK_STREETS_CSYMBOL has considerably worse selectivity that IX_STREETS_LINK_ALT you may consider preventing FK_STREETS_CSYMBOL from being used.

I don't understand this. Both indexes are used and it's good as I know.
It's much better to use that foreign key index instead of no index.

> Another thing you also could consider, is whether it is best to start with SRC or STR first in the PLAN. It might be that
> FROM STREETS STR LEFT JOIN ZLEVELS SRC is a better choice (but that depends on your data, the optimizer thinks that it is best to start with SRC, but it has no knowledge about the selectivity of the values 1360 vs 2000, 2010 and C1, C4).
>

ZLEVELS - over 7 millions of records, STREETS - almost 3 millions. In
table ZLEVELS are only 2 distinct values of SRC_DIR_NAME - 'C1' and 'C4'
(at this time), around 40000 records with ID_CSYMBOL IN (2000, 2010).

Milan

> Sorry for this answer more being about things to consider rather than direct solutions,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Milan Tomeš - Position
> Sent: 16. april 2010 13:28
> To: Firebird - support
> Subject: [firebird-support] Plan question
>
> Hi all,
>
> I have this query:
>
> SELECT ID_ZLEVELS
> FROM ZLEVELS SRC
> JOIN STREETS STR
> ON (STR.LINK_ID = SRC.LINK_ID) AND
> (STR.ALTSTREETS = 'F')
> WHERE
> (SRC.SRC_DIR_NAME IN ('C1', 'C4')) AND
> (SRC.ID_CSYMBOL IN (2000, 2010)) AND
> (STR.ID_CSYMBOL = 1360)
>
> and these indexes on table ZLEVELS:
>
> 1. CREATE INDEX IX_ZLEVELS_DIR_NODE ON ZLEVELS (SRC_DIR_NAME, NODE_ID);
> 2. CREATE INDEX IX_ZLEVELS_DIR_SYM ON ZLEVELS (SRC_DIR_NAME, ID_CSYMBOL);
>
> Why this query produces this plan:
> PLAN JOIN (SRC INDEX (IX_ZLEVELS_DIR_NODE, IX_ZLEVELS_DIR_NODE), STR
> INDEX (IX_STREETS_LINK_ALT, FK_STREETS_CSYMBOL))
> ?
>
> I know about effect of index selectivity (in this case is selectivity of
> index 1 0.0000007 and selectivity of index 2 0.5, but thats because I
> haven't already final data - they are prepared in previous step) but I
> can't understand why optimizer chooses first one instead of second one
> which perfectly fits both where conditions.
>
> Thanks for any hint
>
> M. Tomes
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>