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