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