Query Plans: basics / Planos de execução: aspectos básicos

This article is written in Englsih and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version
I've been requested to write here about Informix query plans. The reasons why I always hesitate were partially the same why I should have done it before: It can be a bit confusing, the documentation could be better, and honestly I'm not always 100% sure about all the details (trust me, there can be many), so I think this can be an incomplete effort. In a recent situation I got confused with a simple query plan, and that triggered me to start writing about it. It's a good way to enforce my knowledge and hopefully this is a subject that can be appreciated by most of Informix users.

I have no doubt that if I did a single article about this it would be gigantic and incomplete. Would be harder to read etc. So I decided to write a few (as time permits) posts with relatively short descriptions of aspects of the query plans.

The query plan is supposed to explain how the engine will solve a query. In it we will obtain the access methods to the tables involved and the methods used to join those tables. Additionally we may get evidences of:

• Function calls
• Grouping of records
• Ordering of records
• Creation of temporary structures (like materialization of views)
• Remote query calls

But for now we'll start with the basics. Let's see a query plan:

castelo@primary:informix-> dbaccess stores -

Database selected.

> EXECUTE FUNCTION ifx_explain('SELECT COUNT(*) FROM customer c, orders o WHERE c.customer_num = o.customer_num AND c.customer_num = 101');



(expression)  
              QUERY: (OPTIMIZATION TIMESTAMP: 09-08-2014 20:18:32)
              ------
              SELECT COUNT(*) FROM customer c, orders o WHERE c.customer_num = 
              o.customer_num AND c.customer_num = 101
              
              Estimated Cost: 2
              Estimated # of Rows Returned: 1
              
                1) informix.c: INDEX PATH
              
                  (1) Index Name: informix. 100_1
                      Index Keys: customer_num   (Key-Only)  (Serial, fragments
              : ALL)
                      Lower Index Filter: informix.c.customer_num = 101 
              
                2) informix.o: INDEX PATH
              
                  (1) Index Name: informix. 102_4
                      Index Keys: customer_num   (Key-Only)  (Serial, fragments
              : ALL)
                      Lower Index Filter: informix.c.customer_num = informix.o.
              customer_num 
              NESTED LOOP JOIN
              
               

1 row(s) retrieved.

>

The most important points in the output for now are:

1. The "QUERY" tag with the time stamp
2. The estimated cost. Most of the times the optimizer will choose the query plan with the lowest cost. But the estimated cost of different queries should not be compared in order to identify the most "expensive" query.
   In most optimizer bugs, the problem is a miscalculation of a query plan cost taht leads to a bad choice for execution
3. The estimated number of rows. This is relevant to the optimizer decisions also
4. The hierarchical nature of the query plan (notice the indentation of the lines and the numbering
5. We see two access PATHs, one for each table. In this case they are INDEX PATHs. For each one there are details (higher indentation)
6. After each pair of tables we'll see a join method. In this case it's the NESTED LOOP. Roughly speaking this is a loop based on the records we gather from the first access PATH (table customer in our case). For each of those lines we use the second access method to obtain zero, one or more records from the second table

Contrary to what is usual here, I'll try to keep these posts small. So for now, that all I have. In future posts I'll try to cover:

• INDEX PATH access methods
• JOIN methods
• Function calls
• Sequential scan
• Query statistics
• Optimizer hints

Versão Portuguesa

Já me foi sugerido que escrevesse aqui sobre os planos de execução no Informix. As razões pelas quais sempre hesitei em fazê-lo são também em parte as razões porque já o deveria ter feito: Podem ser um pouco confusos, a documentação poderia ser melhor, e honestamente não me sinto 100% seguro em todos os aspetos (e acredite que podem haver muitos). Por tudo isto, este esforço pode parecer incompleto. Numa situação recente num cliente tive dúvidas sobre um plano de execução bastante simples. E isso despoletou a vontade de iniciar este tema. Será uma boa forma de reforçar conhecimentos e questionar-me sobre o tema, sendo que espero que seja um tema que agrade à maioria dos utilizadores Informix

Não tenho dúvidas que se tentasse fazer um único artigo seria enorme e muito incompleto. Seria também difícil de ler etc. Por isso decidi escrever alguns (conforme o tempo permita) artigos com descrições relativamente curtas de aspetos dos planos de execução.

O plano de execução é suposto indicar-nos como é que o motor irá resolver a query. Nele encontraremos os métodos de acesso às tabelas envolvidas e os métodos usados para fazer o JOIN dessas mesmas tabelas. Adicionalmente encontraremos:

• Chamadas a funções
• Agrupamento de registos (GROUP BY)
• Ordenação de registos (ORDER BY)
• Criação de estruturas temporárias (como a materialização de views)
• Chamadas a queries remotas

Mas para já vamos começar com o básico. Vejamos um plano de execução:

castelo@primary:informix-> dbaccess stores -

Database selected.

>  EXECUTE FUNCTION ifx_explain('SELECT COUNT(*) FROM customer c, orders o  WHERE c.customer_num = o.customer_num AND c.customer_num = 101');



(expression)  
              QUERY: (OPTIMIZATION TIMESTAMP: 09-08-2014 20:18:32)
              ------
              SELECT COUNT(*) FROM customer c, orders o WHERE c.customer_num = 
              o.customer_num AND c.customer_num = 101
              
              Estimated Cost: 2
              Estimated # of Rows Returned: 1
              
                1) informix.c: INDEX PATH
              
                  (1) Index Name: informix. 100_1
                      Index Keys: customer_num   (Key-Only)  (Serial, fragments
              : ALL)
                      Lower Index Filter: informix.c.customer_num = 101 
              
                2) informix.o: INDEX PATH
              
                  (1) Index Name: informix. 102_4
                      Index Keys: customer_num   (Key-Only)  (Serial, fragments
              : ALL)
                      Lower Index Filter: informix.c.customer_num = informix.o.
              customer_num 
              NESTED LOOP JOIN
              
               

1 row(s) retrieved.

>

Os aspetos mais importantes para já são:

1. A referência a "QUERY" com a hora de processamento
2. O custo estimado. Na maioria das vezes o optimizador irá escolher o plano que tenha um custo estimado melhor. Mas nunca se deve comparar os custos de queries diferentes para inferir qual será a query mais "pesada"
   Na maioria dos bugs no optimizador, o problema tem origem no mau cálculo do custo estimato que pode levar à escolha de um plano menos eficiente
3. O número estimado de linhas a serem retornadas. Isto também será relevante para as escolhas do optimizador
4. A representação hierárquica do plano de execução (note-se a indentação das linhas e a numeração)
5. Vemos dois acessos (PATHs), um para cada tabela. Neste caso são ambos INDEX PATHs. Existem detalhes para cada um deles, apresentados com uma indentação maior
6. Após cada par de tabelas aparece um método de join. Neste caso o NESTED LOOP. Grosso modo isto representa um loop baseado nos registos obtidos no primeiro método de acesso que obtemos no primeiro acesso (tabela customer no nosso caso). Para cada linha usamos o segundo método de acesso para obter zero, um ou mais registos da segunda tabela que validem as condições

Contrariamente ao que costumo fazer, vou tentar manter os artigos pequenos. Portanto, no que diz respeito a este é o que tenho. Em artigos futuros irei abordar:

• Método de acesso INDEX PATH
• Métodos de JOIN
• Chamadas a funções
• Sequential scans
• Estatísticas de query (de execução)
• Diretivas para o optimizador