Thread: Re: Bad plan on a huge table query

Re: Bad plan on a huge table query

From
Daniel Cristian Cruz
Date:
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
クルズ クリスチアン ダニエル

Re: Bad plan on a huge table query

From
Daniel Cristian Cruz
Date:
I've reduced default_statistics_target to 200, and saw less nested loops.

I'm trying some changes to the query, but no better results.

The table presenca has 26 million rows. The table aula_confirmacao has 840 thousand rows.


2013/3/21 Daniel Cristian Cruz <danielcristian@gmail.com>
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
クルズ クリスチアン ダニエル

Re: Bad plan on a huge table query

From
Daniel Cristian Cruz
Date:
And now, it runs, at least:


No one could send a guess on it?


2013/3/21 Daniel Cristian Cruz <danielcristian@gmail.com>
I've reduced default_statistics_target to 200, and saw less nested loops.

I'm trying some changes to the query, but no better results.

The table presenca has 26 million rows. The table aula_confirmacao has 840 thousand rows.


2013/3/21 Daniel Cristian Cruz <danielcristian@gmail.com>
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
クルズ クリスチアン ダニエル



--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル

Re: Bad plan on a huge table query

From
Jeff Janes
Date:
On Thu, Mar 21, 2013 at 10:53 AM, Daniel Cristian Cruz <danielcristian@gmail.com> wrote:
And now, it runs, at least:


No one could send a guess on it?

Without showing either the query, or the definition of the tables, you are not giving us much to go on.

I'm particularly interested in what inicio is and what idx_aula_confirmacao_2 is.  If inicio is already a date, why is being cast to date?  And if it is not already a date, how can it be used in the index scan?



        ->  Index Scan using idx_aula_confirmacao_2 on aula_confirmacao  (cost=0.01..7582.88 rows=4442 width=24) (actual time=64.017..81747.794 rows=101508 loops=1)"
                          Index Cond: (((inicio)::date >= date_trunc('YEAR'::text, now())) AND ((inicio)::date <= now()))"

 Cheers,

Jeff