Thread: Bad plan on a huge table query
Hi,
I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it:
It returns if disable nested loops, but the plan still poor:
I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.
I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling.
Does someone has some idea on that?
Thanks,
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
On Mar 20, 2013, at 22:36, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:
Hi,I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it:
That plan contains no actual statistics, which makes it difficult to say anything about it. And you didn't provide any info on table definitions or indexes whatsoever, we have to put that together from the generated query plans. Not great...
You could probably gain some here by adding an index on aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over more than a million rows.
What I also find a bit peculiar is that the filter in step 7 appears to apply a function (date_part(text, date)) on every row in that heap. Do you perhaps have a functional index on that table that makes that operation efficient?
Besides, now() results in a timestamp, which in this query needs to be converted to date; it's perhaps better to use CURRENT_DATE there, although the benefits are probably immeasurable since it only needs to be calculated once for all rows it's compared against.
I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling.Does someone has some idea on that?
Are you asking about vacuum? You're definitely not vacuuming enough, your statistics and your actual numbers of rows differ by enormous amounts (that's why depesz colours them red).
Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
2013/3/21 Alban Hertroys <haramrae@gmail.com>
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
On Mar 20, 2013, at 22:36, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:Hi,I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it:That plan contains no actual statistics, which makes it difficult to say anything about it. And you didn't provide any info on table definitions or indexes whatsoever, we have to put that together from the generated query plans. Not great...
My bad... I guess the plan could do it. And now I figured out that I lost the first query... Now the query looks like this:
You could probably gain some here by adding an index on aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over more than a million rows.
There is already an index on id_medicao. It used a hashjoin because I disable mergejoin which uses the index, instead there is no return.
What I also find a bit peculiar is that the filter in step 7 appears to apply a function (date_part(text, date)) on every row in that heap. Do you perhaps have a functional index on that table that makes that operation efficient?
Yes, tried to improve performance creating a index on inicio using CAST(inicio TO DATE). The real filter here is aula_confirmacao.inicio::date BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP (filtering all rows from year's beginning until now).
Besides, now() results in a timestamp, which in this query needs to be converted to date; it's perhaps better to use CURRENT_DATE there, although the benefits are probably immeasurable since it only needs to be calculated once for all rows it's compared against.
DATE_TRUNC expect a text and a timestamp.
I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling.Does someone has some idea on that?Are you asking about vacuum? You're definitely not vacuuming enough, your statistics and your actual numbers of rows differ by enormous amounts (that's why depesz colours them red).
autovacuum is running on production and the develop database. This is happening at develop database, fresh restore.
Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.
I've done it; with default_statistics_target on 1000, 100 and 200 (left it on 200, which was production config too).
Thank you and sorry about the broken english, there was a long time since the last time I wrote...
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Ooops, no query... Now it goes (Jeff, types in each line):
SELECT
aluno_mensal.id_matricula, --integer
aluno_mensal.id_turma, --integer
aluno_mensal.turma, --text
aluno_mensal.id_produto_educacao, --integer
aluno_mensal.produto_educacao, --text
aluno_mensal.unidade, --text
aluno_mensal.unidade_execucao, --text
aluno_mensal.modalidade, --text
aluno_mensal.id_pessoa, --integer
aluno_mensal.nome_pessoa, --text
presenca.id_diario, --integer
aula_confirmacao.inicio::date AS data_aula, --timestamp to date
presenca.justificativa_falta, --text
SUM(aula_confirmacao.termino - aula_confirmacao.inicio) AS carga_faltas, --interval
mensal.ano AS ano_apuracao, --integer
mensal.mes AS mes_apuracao --integer
FROM indicadores.aluno_mensal
JOIN indicadores.mensal
ON mensal.id_mensal = aluno_mensal.id_mensal
JOIN turma.presenca
ON presenca.id_matricula = aluno_mensal.id_matricula
JOIN turma.aula_confirmacao
ON aula_confirmacao.id_evento = presenca.id_evento
JOIN turma.estudante_periodo
ON
estudante_periodo.id_matricula = presenca.id_matricula AND
estudante_periodo.id_diario = presenca.id_diario AND
aula_confirmacao.inicio::date BETWEEN estudante_periodo.inicio AND estudante_periodo.termino -- timestamp, date, date
WHERE
presenca.inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND -- timestamp
NOT presenca.presente AND --boolean
mensal.ano = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND -- integer
aula_confirmacao.inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND --timestamp to date
aula_confirmacao.confirmada AND -- boolean
aluno_mensal.id_medicao = 7
GROUP BY
aluno_mensal.id_matricula,
aluno_mensal.id_turma,
aluno_mensal.turma,
aluno_mensal.id_produto_educacao,
aluno_mensal.produto_educacao,
aluno_mensal.unidade,
aluno_mensal.unidade_execucao,
aluno_mensal.modalidade,
aluno_mensal.id_pessoa,
aluno_mensal.nome_pessoa,
presenca.id_diario,
aula_confirmacao.inicio::date,
presenca.justificativa_falta,
mensal.ano,
mensal.mes;
2013/3/21 Daniel Cristian Cruz <danielcristian@gmail.com>
2013/3/21 Alban Hertroys <haramrae@gmail.com>On Mar 20, 2013, at 22:36, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:Hi,I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it:That plan contains no actual statistics, which makes it difficult to say anything about it. And you didn't provide any info on table definitions or indexes whatsoever, we have to put that together from the generated query plans. Not great...My bad... I guess the plan could do it. And now I figured out that I lost the first query... Now the query looks like this:You could probably gain some here by adding an index on aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over more than a million rows.There is already an index on id_medicao. It used a hashjoin because I disable mergejoin which uses the index, instead there is no return.What I also find a bit peculiar is that the filter in step 7 appears to apply a function (date_part(text, date)) on every row in that heap. Do you perhaps have a functional index on that table that makes that operation efficient?Yes, tried to improve performance creating a index on inicio using CAST(inicio TO DATE). The real filter here is aula_confirmacao.inicio::date BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP (filtering all rows from year's beginning until now).Besides, now() results in a timestamp, which in this query needs to be converted to date; it's perhaps better to use CURRENT_DATE there, although the benefits are probably immeasurable since it only needs to be calculated once for all rows it's compared against.DATE_TRUNC expect a text and a timestamp.I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling.Does someone has some idea on that?Are you asking about vacuum? You're definitely not vacuuming enough, your statistics and your actual numbers of rows differ by enormous amounts (that's why depesz colours them red).autovacuum is running on production and the develop database. This is happening at develop database, fresh restore.Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.I've done it; with default_statistics_target on 1000, 100 and 200 (left it on 200, which was production config too).Thank you and sorry about the broken english, there was a long time since the last time I wrote...--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
On Thu, Mar 21, 2013 at 12:30 PM, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:
Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.I've done it; with default_statistics_target on 1000, 100 and 200 (left it on 200, which was production config too).
You are doing an manual analyze each time you change default_statistics_target, right?
Can you do an "analyze verbose aula_confirmacao" and see if the output is as expected?
what happens if you do:
explain (analyze, buffers)
select count(*) from aula_confirmacao where
inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP;
From your explain plan http://explain.depesz.com/s/GDJn, step 9, the row estimates for that simple query should be off by a factor of 23 (101508/4442), yet there is no apparent reason for that to give a bad estimate, other than bad statistics. There are no filters so cross-column correlations can't be throwing it off, so why is it so bad?
Also, it would be nice to see:
select * from pg_stats where tablename = 'idx_aula_confirmacao_2' \x\g\x
(which I assume is a function-based index)
Cheers,
Jeff
Well, I did it:
explain (analyze, buffers)
select count(*) from turma.aula_confirmacao where
inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP; -- changed name because of a conflict in some queries
And just to update, this is the actual query and plan:
EXPLAIN ANALYZE
SELECT
aluno_mensal.id_matricula,
aluno_mensal.id_turma,
aluno_mensal.turma,
aluno_mensal.id_produto_educacao,
aluno_mensal.produto_educacao,
aluno_mensal.unidade,
aluno_mensal.unidade_execucao,
aluno_mensal.modalidade,
aluno_mensal.id_pessoa,
aluno_mensal.nome_pessoa,
presenca.id_diario,
aula_confirmacao.inicio_aula::date AS data_aula,
presenca.justificativa_falta,
SUM(aula_confirmacao.termino_aula - aula_confirmacao.inicio_aula) AS carga_faltas,
mensal.ano AS ano_apuracao,
mensal.mes AS mes_apuracao
FROM indicadores.aluno_mensal
JOIN indicadores.mensal
ON mensal.id_mensal = aluno_mensal.id_mensal
JOIN turma.presenca
ON presenca.id_matricula = aluno_mensal.id_matricula
JOIN turma.aula_confirmacao
ON aula_confirmacao.id_evento = presenca.id_evento
JOIN turma.estudante_periodo
ON
estudante_periodo.id_matricula = presenca.id_matricula AND
estudante_periodo.id_diario = presenca.id_diario AND
aula_confirmacao.inicio_aula::date BETWEEN estudante_periodo.inicio AND estudante_periodo.termino
WHERE
presenca.inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND
NOT presenca.presente AND
mensal.ano = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND
aula_confirmacao.inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND
aula_confirmacao.confirmada AND
aluno_mensal.id_medicao = 7
GROUP BY
aluno_mensal.id_matricula,
aluno_mensal.id_turma,
aluno_mensal.turma,
aluno_mensal.id_produto_educacao,
aluno_mensal.produto_educacao,
aluno_mensal.unidade,
aluno_mensal.unidade_execucao,
aluno_mensal.modalidade,
aluno_mensal.id_pessoa,
aluno_mensal.nome_pessoa,
presenca.id_diario,
aula_confirmacao.inicio_aula::date,
presenca.justificativa_falta,
mensal.ano,
mensal.mes;
I guess that, there is something with estudante_periodo, because there is 24% with only one row and 50% with 5 or less rows on it:
with distr as (select id_matricula, count(*) from turma.estudante_periodo group by id_matricula)
select count as rows_on_estudante_periodo, count(*), (100 * count(*) / sum(count(*)) over ())::numeric(5,2) as percent from distr group by count order by 1;
rows_on_estudante_periodo | count | percent
---------------------------+-------+---------
1 | 24941 | 23.92
2 | 5720 | 5.49
3 | 5220 | 5.01
4 | 8787 | 8.43
5 | 7908 | 7.58
6 | 7357 | 7.06
7 | 4896 | 4.70
8 | 3076 | 2.95
9 | 2963 | 2.84
10 | 2679 | 2.57
11 | 6613 | 6.34
12 | 8708 | 8.35
13 | 4448 | 4.27
14 | 1411 | 1.35
15 | 2137 | 2.05
16 | 1219 | 1.17
17 | 2269 | 2.18
18 | 627 | 0.60
19 | 332 | 0.32
20 | 325 | 0.31
21 | 213 | 0.20
22 | 127 | 0.12
23 | 113 | 0.11
24 | 144 | 0.14
25 | 862 | 0.83
26 | 784 | 0.75
27 | 131 | 0.13
28 | 79 | 0.08
29 | 35 | 0.03
30 | 136 | 0.13
31 | 1 | 0.00
33 | 1 | 0.00
36 | 1 | 0.00
38 | 1 | 0.00
39 | 1 | 0.00
40 | 1 | 0.00
(36 rows)
After the refactoring, idx_aula_confirmacao_2 became idx_aula_confirmacao_1:
select * from pg_stats where tablename = 'idx_aula_confirmacao_1';
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | turma
tablename | idx_aula_confirmacao_1
attname | inicio_aula
inherited | f
null_frac | 0.996792
avg_width | 4
n_distinct | 24
most_common_vals |
most_common_freqs |
histogram_bounds | {2013-02-04,2013-02-25,2013-03-12,2013-03-15,2013-03-19,2013-03-21,2013-03-22,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25}
correlation | 0.433041
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
I've run:
analyze turma.aula_confirmacao ;
select * from pg_stats where tablename = 'idx_aula_confirmacao_1';
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | turma
tablename | idx_aula_confirmacao_1
attname | inicio_aula
inherited | f
null_frac | 0.996927
avg_width | 4
n_distinct | 24
most_common_vals |
most_common_freqs |
histogram_bounds | {2013-02-04,2013-03-04,2013-03-08,2013-03-11,2013-03-13,2013-03-18,2013-03-20,2013-03-22,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25}
correlation | 0.208954
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
and a third time:
analyze turma.aula_confirmacao ;
select * from pg_stats where tablename = 'idx_aula_confirmacao_1';
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | turma
tablename | idx_aula_confirmacao_1
attname | inicio_aula
inherited | f
null_frac | 0.997112
avg_width | 4
n_distinct | 17
most_common_vals |
most_common_freqs |
histogram_bounds | {2013-02-13,2013-03-11,2013-03-15,2013-03-21,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25,2013-03-25}
correlation | 0.459312
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
Thanks,
2013/3/21 Jeff Janes <jeff.janes@gmail.com>
On Thu, Mar 21, 2013 at 12:30 PM, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.I've done it; with default_statistics_target on 1000, 100 and 200 (left it on 200, which was production config too).You are doing an manual analyze each time you change default_statistics_target, right?Can you do an "analyze verbose aula_confirmacao" and see if the output is as expected?what happens if you do:explain (analyze, buffers)select count(*) from aula_confirmacao whereinicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP;From your explain plan http://explain.depesz.com/s/GDJn, step 9, the row estimates for that simple query should be off by a factor of 23 (101508/4442), yet there is no apparent reason for that to give a bad estimate, other than bad statistics. There are no filters so cross-column correlations can't be throwing it off, so why is it so bad?Also, it would be nice to see:select * from pg_stats where tablename = 'idx_aula_confirmacao_2' \x\g\x(which I assume is a function-based index)Cheers,Jeff
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
On Tue, Mar 26, 2013 at 5:08 AM, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:
Well, I did it:explain (analyze, buffers)select count(*) from turma.aula_confirmacao whereinicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP; -- changed name because of a conflict in some queries
That looks reasonable now. But what changed?
And just to update, this is the actual query and plan:...
The part of this complex query that corresponds to that simple query now looks reasonable, while before it did not. What changed in the mean time? Whatever it is that changed (a good vacuum analyze?), you probably need to do the same thing on idx_presenca_3 .
But I see that the difference is not that the estimate moved, but rather that the number of actual rows moved, to be closer to the estimate. I don't know what to make of that.
I guess that, there is something with estudante_periodo, because there is 24% with only one row and 50% with 5 or less rows on it:
That could be, but I don't really know how to tackle that. So I'm starting at the bottom level queries that are simple (i.e. just an index scan, no complex joins or filters) and yet are poorly estimated, because those ones are tractable. Maybe the horrible estimates at the top are just the cumulation of bad estimates at the bottom (but maybe they are not--but still it seems to make sense the tackle the easy ones first)
> After the refactoring, idx_aula_confirmacao_2 became idx_aula_confirmacao_1:
select * from pg_stats where tablename = 'idx_aula_confirmacao_1';
Alas, this information is not so interesting anymore, because now the query is getting the estimate at this step pretty close. It is back when the estimate was so horrible that this info would be interesting.
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------schemaname | turmatablename | idx_aula_confirmacao_1attname | inicio_aulainherited | fnull_frac | 0.996792
But, with the null_frac so high, a partial index for "where ... not null" might be a good idea. I doubt it would make the query change the execution plan, but it would at least take up less disk space and so be more cacheable.
Cheers,
Jeff