Re: Bad plan on a huge table query - Mailing list pgsql-general

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:

http://explain.depesz.com/s/GRs

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...

It returns if disable nested loops, but the plan still poor:

http://explain.depesz.com/s/fMY

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.