1

Topic: : Firebird 3.0 - the optimizer and the expanded plans of requests

Today led  on a subject
Firebird 3.0 - the optimizer and the expanded plans of requests
It  we open a series  about new possibilities Firebird 3.
Ask questions on , sentences on the future subjects, etc.
Answers to questions on  will be let out as separate video in the near future.
[youtube=0KITHwMNDtw]

2

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Register on following :
[url = [youtube=0KITHwMNDtw]] "Architecture Firebird 3: SuperServer, Classic and Embedded" [/url]

3

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Register on following :
"Architecture Firebird 3: SuperServer, Classic and Embedded"

4

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

I in confusion from a penultimate slide.
Let's admit, at me table Docs from 5.000.000 records, and I should load 500  with known keys.
I simply do select * from Docs where DocId in (123,124,145,148...)
And all is quickly loaded.
If to follow to council and to rewrite on where DocId+0 in (...)
There is all very slowly and is sad.

5

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

All business in the question price, is explicit 500 calls to an index more cheaply 5   but if there is still cut-off, for example, by date, and  the PC document there can be more cheaply one reversal to an index by date and further already search on , than 500 times  and can be eliminated an index and that the optimizer did not suffice an index on its PC only +0.
Again , if at you so a lot of known  it is possible to encounter very easily restriction on length of request. Surrenders to me will pour in  more cheaply in  a label and  with it, than to write .
And if not 500, we tell 5000?

6

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah wrote:

I simply do select * from Docs where DocId in (123,124,145,148...)
And all is quickly loaded.

Probably, because the index on docid unique, and there goes 500 searches on one value (that is well visible in explain plan).
In the core field in (...) use with nonunique indexes about what I and spoke. There still there is a nuance, about search of serial and inconsistent values. I will tell separately.

7

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

wrote:

and unless with five hundred values in the list in () - not ?

About 1500 values are admissible, plus there was a restriction on the size of the text of request in 64 earlier.

8

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

kdv wrote:

In the core field in (...) use with nonunique indexes about what I and spoke

Thanks, became more clear now.
Also it, probably, explains the phenomenon that join on primary key much faster, than on other indexed field of the table.
I could not understand it in any way, thought that in both cases identical search in a key in an index is used.

9

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Ivan_Pisarevsky wrote:

Again , if at you so a lot of known  it is possible to encounter very easily restriction on length of request. And if not 500, we tell 5000?

I break into units on 500

Ivan_Pisarevsky wrote:

Surrenders to me will pour in  more cheaply in  a label and  with it, than to write .

Here the problem will transfer 500 , it very slowly.
All I will not wait in any way when make bulk insert

10

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah;
Already made in 4.0. But new API it is necessary still to be able to use

11

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah> the phenomenon that join on primary key much faster, than on other indexed field of the table.
Asyas? It whence?

12

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah wrote:

I in confusion from a penultimate slide.
Let's admit, at me table Docs from 5.000.000 records, and I should load 500  with known keys.
I simply do select * from Docs where DocId in (123,124,145,148...)
And all is quickly loaded.
If to follow to council and to rewrite on where DocId+0 in (...)
There is all very slowly and is sad.

I to you one clever a thing will tell, only you do not take offense please (). For all my almost 25-year-old practice with SQL an amount of cases when really it was required kilometer IN in request equally... To zero. This desire to IN always was an attempt consequence to replace natural conditions of a filtration. For example, to receive documents with 5th on 20 number, with the status "". Or a condition on  to the table, say, a city of the counterpart - Muhosransk. These examples - , certainly, but an essence - absence of desire to work the interface and aspiration to be shorted on row-wise  the user.

13

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah wrote:

I could not understand it in any way, thought that in both cases identical search in a key in an index is used.

H'm. Search in an index always the identical. Only indexes can be different - one admits repetitions of values (nonunique), and another - does not admit (unique).
Accordingly, by search on equality some keys can be derived from a nonunique index, and from unique - one.

14

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah wrote:

here the problem will transfer 500 , it very slowly.

the Client somehow got to itself lists of keys, surrenders to me that it subtracted these from the server, instead of invented.
Whence these lists  undertake?

a teddy bear wrote:

aspiration to be shorted on row-wise  the user.

There is at me one such place, where well in any way without such postinternal , despite the spreading filter. I on  write at once  "" to the normal label, any problems with falling off of the client (for any reasons, from fell off  to the cleaner with a mop), restarted an automated workplace all "" on a place. And I then have less than trouble:  regular means without  .

15

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Ivan_Pisarevsky> There is at me one such place, where well in any way without such a post   the internal
Ivan_Pisarevsky> , despite the spreading filter.
Such at all is. But them in any way 500. At all 100.
To steam of tens it will be typed, at most, and those is at local
Madwomen who is better you know the program.

16

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Rustam wrote:

unah> the phenomenon that join on primary key much faster, than on other indexed field of the table.
Asyas? It whence?

From life.
Here an example:

create table Shops (ShopId int not null primary key, Address varchar (256));
create table Orders (OrderId int not null primary key, ShopId int not null, OrderNum varchar (64));
alter table Order add constraint FK_Order_ref_Shop foreign key (ShopId) references Shops (ShopId);

We flood 700 records in Shops, and 5.000.000 in Orders.
Now we compare speed

select * from Shops s join Orders o on o. ShopId=s. ShopId+0

And

select * from Orders o join Shops s on s. ShopId=o. ShopId+0

17

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah;
At first your statements are not true. Read here http://www.ibase.ru/dataaccesspaths/ more in detail
Secondly it is necessary to compare

select count (*) from Shops s join Orders o on o. ShopId=s. ShopId+0

vs

select count (*) from Orders o join Shops s on s. ShopId=o. ShopId+0

To be assured of extraction of all records.
Well and for the sake of interest compare with

select count (*) from Orders o join Shops s on s. ShopId+0=o. ShopId+0

18

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

19

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

Ivan_Pisarevsky wrote:

the Client somehow got to itself lists of keys, surrenders to me that it subtracted these from the server, instead of invented.
Whence these lists  undertake?

Different cases.
Most simple - loading (or any other operation) with the big list of entities on which the user wants to see progress.
Then the first Select query Id from... join....
We learn, how many all will be lines as a result, and then we fulfill operation (we load on the client) packs on 100-1000.
Especially actually, if the client is connected not a DB, and to an application server on HTTP and on each request at the client a limit on time and the size of a packet.
There was still a variant of usage FB as stupid key-value storage.
The hand control  in an application server was applied.
All join become in storage, and lines are loaded under list Id, only what else are not present in storage . Strongly saves kol-in readings from a DB.
And at last. Relations many-to-much.
Master essence in the table from 30 million records, Detail - 3 million records and links in between - more than 300 million (the separate table with links on master and detail for each communication, all on the best experts).
And here is how time join to the table links began to choke (because not on unique index?).
Followers of the pure relational theory can be spat, but the fact is the fact.
After transfer links in the table master it is stupid in a string field varchar (3000) id-shniki through a comma, and reading details under list Id (where DetailId in (121,122,123...)) loading so decreased that the system has been rescued.

a teddy bear wrote:

I to you one clever a thing will tell, only you do not take offense please ()

At you experience is too restricted, and to me to take offense? wink

20

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah;
It is not necessary to produce the special case of allocation of the data in tables for decisions for all. I to you can result examples where all with accuracy on the contrary. Well and buffer Memory buffers = 2 048 for  SS it simply laughter. It is necessary to increase a minimum in 10 times.

unah wrote:

Hesh-join out of competition (triple news? I somehow lost sight of it)

At you Shops scanty, and Orders the big. Here hesh-join that the doctor registered that. HASH JOIN replaced MERGE JOIN in 3.0

21

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

22

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah;
To compare different plans of requests and to do from this outputs about high-speed performance of search in an index is, we tell softly, not cleverly...

23

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

hvlad;
I had well played back observation:
- If to add in request join on primary key, it is possible not to wait for any problems
- Is to add in request join on other indexes, can sharply grow kol-in readings of pages, and such join it is necessary to check up all 100 times.
It is good that in this subject more or less disassembled the reason.

24

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

unah wrote:

PLAN JOIN (O NATURAL, S INDEX (PK_SHOPS))
PLAN JOIN (S NATURAL, O INDEX (FK_ORDER_REF_SHOP))

, yes at what here PC not the PC... The Alphabet, the order of join of tables. The stub search  with pulling up of elements smaller  will be clear, what index use. That is why the optimizer of triple because of simple swap of positions of tables and fields in request, without a magic wand +0, changes the search order - for me a riddle. Never at us such was and here it again?

25

Re: : Firebird 3.0 - the optimizer and the expanded plans of requests

a teddy bear wrote:

Hospidi, yes at what here PC not the PC... The Alphabet, the order of join of tables. The stub search  with pulling up of elements smaller  will be clear, what index use.

Result saw? Search  with pulling up of elements smaller turned out faster wink