Optimizer secrets / segredos do optimizador

This article is written in English and Portuguese

Este artigo está escrito em Inglês e Português

English version:

Spending a lot of time with customers is great. It gives me time to go beyond the strict task execution that short projects allow. We actually have time to dive deep into what sometimes looks only as a curiosity. I'll describe one of this situations in this post. It started out as a normal performance issue. A query involving a join was causing an hash join and apparently there was an index that could be used. Some quick investigation showed that the datatypes didn't match on the join columns, and as such the index was not being used.
The situation was fixed (using an explicit cast since changing the datatypes would require more deep analysis) and the programmer(s) received an explanation about the issue so that future situations would be avoided. Simple right? Yes... But when you have inquiring minds and programmers with a few minutes to spare you may be surprised. And this was the case. A few weeks later the DBA team received a request to explain why we recommended that the columns used in joins should have the same datatype. A programmer had produced a testcase where the engine was able to convert the parameter sent and use the index. In other words, if the engine is smart enough why should we care?!

Although this could be considered a waste of time (using the same datatypes or explicit cast is a good practice, right?!) the question was interesting enough to make us spend some time with it. In fact I had seen situations in the past where apparently sometimes the engine was smart, and others not. I never thought too much about it, since I always recommended to follow the best practices (which clearly saves us some troubles). So, personally I also had this doubt, and together with the customer DBAs we started to do some tests. We came up with a very simple test case that we though would show the problem:

DATABASE stores;

DROP TABLE IF EXISTS tst_int;
DROP TABLE IF EXISTS tst_char;

CREATE TABLE tst_int
(
    c1 INTEGER,
    c2 INTEGER
);
CREATE TABLE tst_char
(
    c1 CHAR(15),
    c2 INTEGER
);

INSERT INTO tst_int
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

INSERT INTO tst_char
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

CREATE INDEX i_tst_int ON tst_int(c1);
CREATE INDEX i_tst_char ON tst_char(c1);

SET EXPLAIN ON;
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678';
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678;

--- demonstrates that each index key is being casted
INSERT INTO tst_char VALUES("a1", 12345678);
SELECT * FROM tst_char WHERE c1 = 12345678;

If we take a look at the query plan we see:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.tst_int: INDEX PATH

    (1) Index Name: informix.i_tst_int
        Index Keys: c1   (Serial, fragments: ALL)
        Lower Index Filter: informix.tst_int.c1 = 12345678

So here the engine was "smart". Meaning it converted the CHAR to an INTEGER and that allowed it to use the index. Nice.


But here:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678

Estimated Cost: 84
Estimated # of Rows Returned: 1

  1) informix.tst_char: SEQUENTIAL SCAN

        Filters: informix.tst_char.c1 = 12345678

It looks as it's not that smart... Instead of converting the INTEGER parameter to a CHAR and use the index it decides to do the opposite: Converts all the CHARs in that column into INTEGERs and makes a SEQUENTIAL SCAN.
Since I didn't have a good explanation for this we decided to open a PMR to get an official technical support explanation.


Technical support reported that we had something in the documentation that tries to explain this:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1620.htm

This documentation is highly confusing but it tells us something important: If we use a numeric filter against a CHAR column, the database server will convert all the CHAR values to numbers. This is precisely what we saw in the example above. But it still does not explain why. Let me show why with a few examples:

1. If we have "00123" in the table and we give 123 as the parameter, if we convert the number to CHAR and try to match it, ir would fail. Bad results...
2. If we have "234" in the column and we give 9 as the parameter for a "less or equal" (col <= 9), if we convert the number to CHAR, and apply the filter to the CHAR type, it would match ("9" >= "234"). Again it would return an incorrect result because by using an INTEGER as a parameter we're assuming INTEGER comparison

So, after this, the rule would seem pretty simple. Something like: "If you use a numeric parameter against a CHAR column, all the values in the column will be converted to numeric.  On the other hand, using a CHAR parameter against a numeric column allows the engine to convert the numeric to CHAR and if there is an index on the column it can be used".

But life's not that simple... If it was so simple why couldn't we find it in the documentation? I tried to search for other RDBMS (Oracle and MS SQL) documentation, and in those cases they're very clear about the issue. Something like "whenever an implicit CAST is needed we follow a precedence table of datatypes. The datatype with lower precedence will be converted into the datatype with higher precedence". Sincerely I thought this was a good way to put it, and if we did the same, why not document it properly? So the PMR already opened started to look like a documentation bug.
But again, life sometimes is not simple... And while this was being analyzed and discussed, the customer team discovered an interesting scenario: If you give an integer value as a filter against a CHAR column, AND the length of the integer value (excluding any leading zeros) is equal to the size of the column, than Informix will convert the number to CHAR and eventually will use an index on the specified column.
This is the optimizer being smart. If you think about it, if the number has the same number of digits as the length of the CHAR column, you can convert the number to CHAR and compare it. The result set will be correct no matter the values in question or the operator.

To end the story, while browsing the documentation in search for other topics we came across this:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.perf.doc/ids_prf_536.htm

It's clear and well explained. Informix makes the necessary casts but that can have a real impact on the performance, specially if exists an index on the column. And the optimizer is smart enough to use a better query plan in the only situation where it can be done. Really nice and at least here (Performance Guide) it's well explained. I really don't mind when a PMR generates a bug because that's a product improvement, but I must admit I prefer to be proven wrong and see that the product really works well!


Versão Portuguesa:

É ótimo passar muito tempo com clientes. Dá-me tempo para ir para além da estrita execução de tarefas que os projetos curtos permitem. Temos tempo para aprofundar o que por vezes não parece ser mais que uma curiosidade. Nesta entrada vou descrever uma dessas situações. Começou como um banal problema de performance. Uma query que envolvia um join estava a gerar um hash join havendo um índice que aparentemente podia ser usado. Após uma rápida investigação percebeu-se que os tipos de dados das colunas envolvidas no join não eram iguais e por isso o índice não era usado.
A situação foi corrigida (usando um CAST explícito pois mudar os tipos de dados teria necessitado de uma análise mais profunda e poderia ter outras implicações) e o programador(es) recebeu uma explicação sobre o problema para que situações semelhantes fossem evitadas no futuro. Simples, certo? Sim... Mas quando temos mentes curiosas e programadores com alguns minutos para dispensar podemos ser surpreendidos. E este foi um desses casos. Umas semanas mais tarde a equipa de DBAs do cliente recebeu um pedido para explicar o porquê da recomendação, acompanhado de um caso de teste que demonstrava que o motor conseguia usar um índice mesmo quando os tipos de dados não batiam certo. Por outras palavras, se o motor tem inteligência para o fazer, porque nos devemos nós preocupar?!


Apesar de isto poder ser considerado uma perda de tempo (usar os mesmos tipos de dados ou um CAST explícito é uma boa prática, não é?!) a questão era suficientemente interessante para nos fazer gastar algum tempo com ela. Na realidade já tinha tido situações no passado onde aparentemente o motor parecia inteligente, e outras onde tal não acontecia. Nunca pensei muito no assunto, dado que recomendo sempre que seja seguida as boas práticas (que claramente nos evitam problemas). Portanto, pessoalmente também tinha esta dúvida e em conjunto com os DBAs do cliente iniciámos alguns testes. Criámos um exemplo muito simples que pensamos que demonstra o problema:

DATABASE stores;

DROP TABLE IF EXISTS tst_int;
DROP TABLE IF EXISTS tst_char;

CREATE TABLE tst_int
(
    c1 INTEGER,
    c2 INTEGER
);
CREATE TABLE tst_char
(
    c1 CHAR(15),
    c2 INTEGER
);

INSERT INTO tst_int
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

INSERT INTO tst_char
SELECT FIRST 2000 pg_pagenum, pg_partnum FROM sysmaster:syspaghdr;

CREATE INDEX i_tst_int ON tst_int(c1);
CREATE INDEX i_tst_char ON tst_char(c1);

SET EXPLAIN ON;
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678';
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678;

--- demonstrates that each index key is being casted
INSERT INTO tst_char VALUES("a1", 12345678);
SELECT * FROM tst_char WHERE c1 = 12345678;

Se olharmos para o plano de execução vemos:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_int with char parameter:", * FROM tst_int WHERE c1 = '12345678'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.tst_int: INDEX PATH

    (1) Index Name: informix.i_tst_int
        Index Keys: c1   (Serial, fragments: ALL)
        Lower Index Filter: informix.tst_int.c1 = 12345678

Ou seja, aqui o motor era "esperto". Convertia o CHAR para INTEGER e isso permitia usar o índice. Boa.

Mas aqui:

QUERY: (OPTIMIZATION TIMESTAMP: 12-10-2011 23:25:19)
------
SELECT "tst_char whth int parameter",* FROM tst_char WHERE c1 = 12345678

Estimated Cost: 84
Estimated # of Rows Returned: 1

  1) informix.tst_char: SEQUENTIAL SCAN

        Filters: informix.tst_char.c1 = 12345678

Parece que não é assim tão esperto.... Em vez de converter o parâmetro INTEGER para um CHAR e usar o índice, decide fazer o oposto: Converte todos os CHARs daquela coluna para INTEGERs e executa um SEQUENTIAL SCAN.
Como não tinha uma boa explicação para isto decidi abrir um PMR para obter uma explicação oficial do suporte técnico.


O suporte técnico informou que nós tínhamos algo na documentação que tenta explicar isto:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.sqls.doc/ids_sqs_1620.htm

Esta documentação é altamente confusa, mas diz-nos algo importante: Se usarmos um filtro numérico contra uma coluna do tipo CHAR, o servidor de base de dados irá converter todos os valores CHAR da coluna em números. Isto é exatamente o que encontrámos no exemplo acima. Mas ainda não explica porquê. Deixe-me explicar o porquê com alguns exemplos:

1. Se tivermos o valor "00123" na tabela e usarmos 123 como parâmetro/filtro, se convertermos o número para CHAR e tentarmos fazer a comparação não vamos retornar nada ('123' != '00123') . Resultados errados...
2. Se tivermos o valor "234" na coluna e dermos 9 como parâmetro/filtro para uma condição de menor ou igual (col <= 9), se convertermos o número para CHAR isso implicaria que a linha era retornada  ("9" >= "234"). Mais uma vez iria retornar um resultado "errado", pois ao usarmos um parâmetro numérico estamos a assumir comparação numérica (onde 9 < 234)

Assim, depois disto a regra parecia muito simples. Algo como "Se usarmos um parâmetro numérico contra uma coluna do tipo CHAR, todos os valores da coluna serão convertidos para numérico. Por outro lado, usar um parâmetro CHAR contra uma coluna numérica permite que o motor converta o parâmetro para número e use um índice caso exista".

Mas a vida não é assim tão simples... Se era assim tão direto porque razão não estava documentado (ou pelo menos nós não tínhamos encontrado)? Tentei procurar na documentação de outros sistemas de gestão de bases de dados (Oracle e MS SQL) , e nestes casos eram bastante claros sobre o assunto. Um resumo livre seria "sempre que um CAST implícito seja necessário, seguimos uma tabela de precedências de tipos de dados. O tipo de dado com menor precedência será convertido para o que tem mais precedência". Sinceramente isto pareceu-me uma forma correta de colocar a questão, e se fazíamos o mesmo porque não ter isto claro na documentaçã? Assim o PMR já aberto parecia encaminhar-se para um bug de documentação.

Mas novamente, a vida por vezes não é simples... E enquanto isto estava a ser analisado e discutido , a equipa do cliente descobriu um cenário interessante: Se usarmos um numero como filtro contra uma coluna do tipo CHAR, e o numero de dígitos desse inteiro (excluindo quaisquer zeros à esquerda) for igual ao número de caracteres definido na coluna, então o número será convertido para CHAR e um eventual índice na coluna será usado.
Isto é o optimizador a ser "esperto". Se pensarmos sobre o assunto, se o número de dígitos do número for igual ao número de caracteres da coluna, podemos convertê-lo para CHAR e compará-lo com a coluna. O resultado será o correto independentemente dos valores em questão e do operador.


Para terminar a história, enquanto consultáva-mos a documentação devido a outro assunto, demos com o seguinte:

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.perf.doc/ids_prf_536.htm

Está claro e bem explicado. O Informix efetua os CASTS necessários para resolver queries onde existam inconsistências entre os tipos de dados. Mas isso pode ter um impacto significativo na performance, especialmente se existir um índice na coluna. E o optimizador é suficientemente inteligente para obter um melhor plano de execução na única situação onde isso pode ser feito. Muito correto e pelo menos aqui (Guia de Performance) está bem explicado.
Sinceramente não me importo muito quando um PMR dá origem a um bug, pois isso traduz-se numa melhoria do produto, mas tenho de admitir que prefiro que resulte que estava enganado e ser-me mostrado que o produto está a funcionar bem!

Let's optimize / Vamos optimizar

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version.

In this article I'll show you a curious situation related to the Informix query optimizer. There are several reasons to do this. First, this is a curious behavior which by itself deserves a few lines. Another reason is that I truly admire the people who write this stuff... I did and I still do some programming, but my previous experience was related to business applications and currently it's mostly scripts, small utilities, stored procedures etc. I don't mean to offend anyone, but I honestly believe these two programming worlds are on completely different leagues. All this to say that a query optimizer is something admirable given the complexity of the issues it has to solve.

I also have an empiric view or impression regarding several query optimizers in the database market. This impression comes from my personal experience, my contact with professionals who work with other technologies and some readings... Basically I think one of our biggest competitors has an optimizer with inconstant behavior, difficult to understand and not very trustworthy. One of the other IBM relational databases, in this case DB2, has probably the most "intelligent" optimizer, but I have nearly no experience with it. As for Informix, I consider it not the most sophisticated (lot of improvements in V10 and V11, and more to come in Panther), but I really love it for being robust. Typically it just needs statistics. I think I only used or recommended directives two times since I work with Informix. And I believe I never personally hit an optimizer bug (and if you check the fix lists in each fixpack you'll see some of them there...)
To end the introduction I'd like to add that one of my usual tasks is query optimizing... So I did catch a few interesting situations. As I wrote above, typically an UPDATE statistics solves the problem. A few times I saw situations where I noticed optimizer limitations (things could work better if it could solve the query another way), or even engine limitations (the query plan is good, but the execution takes unnecessary time to complete). The following situation was brought to my attention by a customer who was looking around queries using a specific table which had a reasonable number of sequential scans.... Let's see the table schema first:

create table example_table
(
et_id serial not null ,
et_ts datetime year to second,
et_col1 smallint,
et_col2 integer,
et_var1 varchar(50),
et_var2 varchar(50),
et_var3 varchar(50),
et_var4 varchar(50),
et_var5 varchar(50),
primary key (et_id)
)
lock mode row;

create index ids_example_table_1 on example_table (et_timestamp) using btree  in dbs1

And then two query plans they manage to obtain:

QUERY: (OPTIMIZATION TIMESTAMP: 08-25-2010 12:31:56)
------
SELECT  et_ts, et_col2
FROM example_table WHERE et_var2='LessCommonValue'
AND et_col1 = 1 ORDER BY et_ts DESC


Estimated Cost: 5439
Estimated # of Rows Returned: 684
Temporary Files Required For: Order By

1) informix.example_table: SEQUENTIAL SCAN

Filters: (informix.example_table.et_var2 = 'LessCommonValue' AND informix.example_table.et_col1 = 1 )

So this was the query responsible for their sequential scans. Nothing special. The conditions are on non indexed fields. So no index is used. It includes an ORDER BY clause, so a temporary file to sort is needed (actually it can be done in memory, but the plan means that a sort operation has to be done).
So, nothing noticeable here... But let's check another query plan on the same table. In fact, the query is almost the same:

QUERY: (OPTIMIZATION TIMESTAMP: 08-25-2010 12:28:32)
------
SELECT  et_ts, et_col2
FROM example_table WHERE et_var2='MoreCommonValue'
AND et_col1 = 1 ORDER BY et_ts DESC


Estimated Cost: 5915
Estimated # of Rows Returned: 2384

1) informix.example_table: INDEX PATH

Filters: (informix.example_table.et_var2 = 'MoreCommonValue' AND informix.example_table.et_col1 = 1 )

(1) Index Name: informix.ids_example_table_1
Index Keys: et_ts   (Serial, fragments: ALL)

Ok... It's almost the same query, but this time we have an INDEX path.... Wait... Didn't I wrote that the conditions where not on indexed column(s)? Yes. Another difference: It does not need a temporary file for sort (meaning that it's not doing a sort operation). Also note that there are no filter applied on the index. So, in this case it's doing a completely different thing than on the previous situation. It reads the index (the whole index), accesses the rows (so it gets them already ordered) and then applies the filters discarding the rows that do not match. It does that because it wants to avoid the sort operation. But why does it change the behavior? You might have noticed that I already included a clue about this. To protect data privacy, I changed the table name, table fields and the query values. And in the first query I used "LessCommonValue" and on the second I used "MoreCommonValue". So as you might expect, the value on the second query is more frequent in the table than the value of the first query. How do we know that? First, by looking at the number of estimated rows returned (684 versus 2384). How does it predict this values? Because the table has statistics, and in particular column distribution. Here are them for the et_var2 column (taken with dbschema -hd):

Distribution for informix.example_table.et_var2

Constructed on 2010-08-25 12:19:04.31216

Medium Mode, 2.500000 Resolution, 0.950000 Confidence


--- DISTRIBUTION ---

( AnotherValue                                               )


--- OVERFLOW ---

1: (  4347,         Value_1                                            )
2: (  4231,         Value_2                                            )
3: (  3129,         MoreCommonValue                        )
4: (  2840,         Value_4                                            )
5: (  2405,         Value_5                                            )
6: (  3854,         Value_6                                            )
7: (  3941,         Value_7                                            )
8: (  2086,         Value_8                                            )
9: (  4086,         Value_9                                            )
10: (  4144,         Value_10                                           )
11: (  4086,         Value_11                                           )
12: (  2666,         Value_12                                           )
13: (  2869,         Value_13                                           )
14: (  3245,         Value_14                                           )
15: (  2811,         Value_15                                           )
16: (  3419,         Value_16                                           )
17: (  2637,         Value_17                                           )
18: (  3187,         Value_18                                           )
19: (  4347,         Value_19                                           )
20: (   898,         LessCommonValue                                    )
21: (  4144,         Value_20                                           )
22: (  2260,         Value_21                                           )
23: (  3999,         Value_22                                           )
24: (  1797,         Value_23                                           )
25: (  2115,         Value_24                                           )
26: (  2173,         Value_25                                           )
27: (  4144,         Value_26                                           )

If you're not used to look at this output I'll describe it. The distributions are showed as a list of "bins". Each bin, except the first one, contains 3 columns: The number of record it represents, the number of unique values within that bin, and the highest value within that bin. The above is not a good example because the "normal" bin values has only one (AnotherValue) and given it's the first one it means it's the "lowest" value in the column. Let's see an example from the dbschema description in the migration guide:

( 5)
1: ( 16, 7, 11)
2: ( 16, 6, 17)
3: ( 16, 8, 25)
4: ( 16, 8, 38)
5: ( 16, 7, 52)
6: ( 16, 8, 73)
7: ( 16, 12, 95)
8: ( 16, 12, 139)
9: ( 16, 11, 182)
10: ( 10, 5, 200)

Ok. So, looking at this we could see that the lowest value is 5, and that between 5 and 11 we have 16 values, 7 of them are unique. Between 11 and 17 we have 6 unique values etc.

Going back to our example we see an "OVERFLOW" section. This is created when there are highly repeated values that would skew the distributions. In these cases the repeated values are showed along the number of times they appear.

So, in our case, the values we're interested are "LessCommonValue" (898 times) and "MoreCommonValue" (3129 times). So with these evidences we can understand the decision to choose the INDEX PATH. When the engine gets to the end of the result set it's already ordered. And in this situation (more than 3000 rows) the order by would be much more expensive than for the other value (around 800 rows).

It's very arguable if the choice is effectively correct. But my point is that it cares... Meaning it goes deep in it's analysis, and that it takes into account many aspects, and is able to decide to use a query plan that it's not obvious (and choose it for good reasons).


Versão portuguesa:

Neste artigo vou mostrar uma situação curiosa relacionada com o optimizador de queries do Informix. Há várias razões para fazer isto. Primeiro porque é um comportamento interessante que por si só merece umas linhas. Outra razão é que eu admiro verdadeiramente as pessoas que escrevem este componente.... Eu fiz e ainda faço alguma programação, mas a minha experiência anterior estava relacionada com aplicações de negócio e actualmente é essencialmente scripts, pequenos utilitários, stored procedures etc. Não querendo ofender ninguém, mas honestamente acredito que estes dois mundos de programação estão em campeonatos completamente diferentes. Tudo isto para dizer que o optimizador é algo admirável dada a complexidade dos problemas que ele tem de resolver.

Também tenho uma visão empírica ou apenas uma impressão relativamente a alguns optimizadores no mercado de bases de dados. Esta impressão deriva da minha experiência pessoal, do meu contacto com profissionais que trabalham com outras tecnologias e de alguma leitura... Basicamente penso que o nosso maior competidor tem um optimizador com um comportamento insconstante, difícil de entender e pouco confiável. Uma das outras bases de dados relacionais da IBM, DB2 neste caso, tem provavelmente o optimizador mais "inteligente", mas não tenho practicamente nenhuma experiência com ele. No caso do Informix, considero que não é o mais sofisticado (muitas melhorias na versão 10 e 11, e mais no Panther), mas realmente adoro a sua robustez. Tipicamente apenas precisa de estatísticas. Julgo que só utilizei ou recomendei optimizer directives duas vezes desde que trabalho com Informix. E que me lembre nunca bati pessoalmente num bug do optimizador (embora baste consultar a lista de correcções de cada fixpack para sabermos que eles existem).
Para terminar esta introdução gostaria de referir que uma das minhas tarefas habituais é a optimização de queries. Por isso já encontrei algumas situações interessantes. Como escrevi acima, tipicamente um UPDATE STATISTICS resolve o problema. Algumas vezes encontrei limitações do optimizador (as coisas poderiam correr melhor se ele pudesse resolver as queries de outra maneira) e até limitações do motor (o plano de execução gerado é bom, mas a execução leva tempo desnecessário a correr). A situação que se segue chegou-me à atenção através de um cliente que estava a analisar queries sobre uma tabela que estava a sofrer muitos sequential scans.... Vejamos a definição da tabela primeiro:

create table example_table
(
et_id serial not null ,
et_ts datetime year to second,
et_col1 smallint,
et_col2 integer,
et_var1 varchar(50),
et_var2 varchar(50),
et_var3 varchar(50),
et_var4 varchar(50),
et_var5 varchar(50),
primary key (et_id)
)
lock mode row;

create index ids_example_table_1 on example_table (et_timestamp) using btree  in dbs1

E agora dois planos de execução que obtiveram:

QUERY: (OPTIMIZATION TIMESTAMP: 08-25-2010 12:31:56)
------
SELECT  et_ts, et_col2
FROM example_table WHERE et_var2='ValorMenosComum'
AND et_col1 = 1 ORDER BY et_ts DESC


Estimated Cost: 5439
Estimated # of Rows Returned: 684
Temporary Files Required For: Order By

1) informix.example_table: SEQUENTIAL SCAN

Filters: (informix.example_table.et_var2 = 'ValorMenosComum' AND informix.example_table.et_col1 = 1 )

Esta era uma das queries responsável pelos sequential scans. Nada de especial. As condições da query incidem em colunas não indexadas. Portanto não utiliza nenhum indíce. Inclui uma cláusula ORDER BY, por isso um necessita de "ficheiros" temporários para ordenação. (na verdade pode ser feito em memória, mas o plano quer dizer que precisa de fazer uma operação de ordenação).
Portanto nada a salientar aqui.... Mas vejamos o outro plano sobre a mesma tabela. Na verdade a query é muito semelhante:

QUERY: (OPTIMIZATION TIMESTAMP: 08-25-2010 12:28:32)
------
SELECT  et_ts, et_col2
FROM example_table WHERE et_var2='ValorMaisComum'
AND et_col1 = 1 ORDER BY et_ts DESC


Estimated Cost: 5915
Estimated # of Rows Returned: 2384

1) informix.example_table: INDEX PATH

Filters: (informix.example_table.et_var2 = 'ValorMaisComum' AND informix.example_table.et_col1 = 1 )

(1) Index Name: informix.ids_example_table_1
Index Keys: et_ts   (Serial, fragments: ALL)

Ok... É quase a mesma query, mas desta vez temos um INDEX PATH... Um momento!.... Não escrevi que as condições não indiciam sobre colunas indexadas? Sim. Outra diferença: Neste caso não necessita de "temporary files" (o que significa que não está a fazer ordenação). Note-se ainda que não existe nenhum filtro aplicado ao indíce. Portanto, neste caso está a fazer algo completamente diferente da situação anterior. Lê o indíce (todo o indíce), acede às linhas (assim obtém-nas já ordenadas) e depois aplica os filtros descartando as linhas que não verificam as condições. Faz isto porque pretende evitar a operação de ordenação. Mas porque é que muda o comportamento? Poderá ter reparado que já inclui uma pista sobre isto. Para proteger a privacidade dos dados mudei o nome da tabela, dos campos e dos valores das queries. E no primeiro caso utilizei "ValorMenosComum" e no segundo usei "ValorMaisComum". Portanto como se pode esperar, o valor da segunda query é mais frequente na tabela que o valor usado na primeira query. Como sabemos isso? Primeiro olhando para o valor estimado de número de linhas retornadas pela query (684 vs 2384). Como é que o optimizador prevê estes valores? Através das estatísticas da tabela, em particular as distribuições de cada coluna. Aqui estão as mesmas para a coluna et_var2 (obtidas com dbschema -hd):

Distribution for informix.example_table.et_var2

Constructed on 2010-08-25 12:19:04.31216

Medium Mode, 2.500000 Resolution, 0.950000 Confidence


--- DISTRIBUTION ---

( OutroValor                                               )


--- OVERFLOW ---

1: (  4347,         Value_1                                            )
2: (  4231,         Value_2                                            )
3: (  3129,         ValorMaisComum                                     )
4: (  2840,         Value_4                                            )
5: (  2405,         Value_5                                            )
6: (  3854,         Value_6                                            )
7: (  3941,         Value_7                                            )
8: (  2086,         Value_8                                            )
9: (  4086,         Value_9                                            )
10: (  4144,         Value_10                                           )
11: (  4086,         Value_11                                           )
12: (  2666,         Value_12                                           )
13: (  2869,         Value_13                                           )
14: (  3245,         Value_14                                           )
15: (  2811,         Value_15                                           )
16: (  3419,         Value_16                                           )
17: (  2637,         Value_17                                           )
18: (  3187,         Value_18                                           )
19: (  4347,         Value_19                                           )
20: (   898,         ValorMenosComum                                    )
21: (  4144,         Value_20                                           )
22: (  2260,         Value_21                                           )
23: (  3999,         Value_22                                           )
24: (  1797,         Value_23                                           )
25: (  2115,         Value_24                                           )
26: (  2173,         Value_25                                           )
27: (  4144,         Value_26                                           )

Para o caso de não estar habituado a analisar esta informação vou fazer uma pequena descrição da mesma. As distribuições são mostradas como uma lista de "cestos". Cada cesto, excepto o primeiro, contém 3 colunas: O número de registo que representa, o número de valores únicos nesse cesto e o valor mais "alto" nesse cesto. O que está acima não é um bom exemplo porque a secção de "cestos" normal só tem um valor (OutroValor), e dado que é o primeiro apenas tem o valor, o que significa que é o valor mais "baixo" da coluna. Vejamos um exemplo retirado da descriçao do utilitário dbschema no manual de migração:

( 5)
1: ( 16, 7, 11)
2: ( 16, 6, 17)
3: ( 16, 8, 25)
4: ( 16, 8, 38)
5: ( 16, 7, 52)
6: ( 16, 8, 73)
7: ( 16, 12, 95)
8: ( 16, 12, 139)
9: ( 16, 11, 182)
10: ( 10, 5, 200)

Portanto olhando para isto podemos ver que o valor mais baixo é 5, que entre 5 e 11 temos 16 valores, 7 dos quais são únicos. Entre 11 e 17 temos 6 valores únicos etc.

Voltando à nossa situação concreta vemos uma secção de "OVERFLOW". Esta secção é criada quando há valores altamente repetidos que iriam deturpar ou desequilibrar as distribuições. Nestes casos os valores repetidos são mostrados lado a lado com o número de vezes que aparecem.

Assim, no nosso caso, estamos interessados nos valores "ValorMenosComum" (898 vezes) e "ValorMaisComum" (3129 vezes). Portanto, com estas evidências podemos perceber a decisão de escolher o INDEX PATH. Quando o motor chega ao final do conjunto de resultados o mesmo já está ordenado. E nesta situação (mais de 3000 linhas) a ordenação consumiria mais recursos qeu para a outra situação em análise (cerca de 800 linhas).


Pode ser muito discutivel se a decisão é efectivamente correcta. Mas o que quero salientar é que o optimizador se preocupa.... Ou seja, a análise que faz das condições e opções é profunda e é capaz de escolher um plano de execução que não é óbvio (e escolhê-lo por boas razões).