Re: SQL Query never ending...

Hello Tom, good evening.

Thank you for your prompt answer and help.

It was enough to ANALYZE the temp tables to achieve a magnificent
result:  37 seconds.

I'm portuguese.
It's a shame you live so far from me. So I can't put a box of bottles of
Porto wine at your door.

I also thank David and Fabrízio for their willingness to help me.
They deserve a bottle of Porto wine each.

Best regards
Dias Costa

On 21-06-2018 22:08, Tom Lane wrote:
> DiasCosta <diascosta(at)diascosta(dot)org> writes:
>> This is the query plan for only 19684 rows.
> I think you're getting a bad query plan, mostly as a result of two
> factors:
>
> * Poor row estimates. It looks like the bottom-most misestimations
> are on temp tables, which makes me wonder whether you've ANALYZEd
> those tables. Your application has to do that explicitly after
> populating the tables; auto-analyze can't help on temp tables.
>
> * Too many tables --- I count 33 table scans in this query. You
> might get better planning results by raising join_collapse_limit
> and/or from_collapse_limit, but it will come at a cost in planning
> time, and in any case a query with this many tables is never likely
> to be cheap. You might want to think about restructuring your schema
> to not need so many tables, or maybe just do some hand optimization
> of the query to eliminate unnecessary joins. (It looks to me like
> at least some of the joins to tt_eotb1 might be unnecessary?)
>
> regards, tom lane
>
>

--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o
malfadado acordo ortográfico.