Very time consuming query depending on query ID

Question

Very time consuming query depending on query ID

Asked 5 years, 9 months ago

Viewed 454 times

0

Hello,

I have a fairly large query that is running in the bank, follows.

SELECT "p"."id", "fr"."apelido", "fr"."compra_minima", "fr"."desconto", "fr"."aplicavel_todocarrinho"
FROM "basket" "b"
JOIN "produtos" "p" ON "p"."id" = "b"."id_produto"
LEFT JOIN "produtos_multicategoria" "pm" ON "pm"."id_produto" = "p"."id"
LEFT JOIN "frete_regrapromocao_categorias" "frc" ON "frc"."id_cateprod" IN (p.id_categoria, pm.id_categoria_1, pm.id_categoria_2,pm.id_categoria_3, pm.id_categoria_4, pm.id_categoria_5)
LEFT JOIN "frete_regrapromocao_fabricante" "frf" ON "frf"."id_fabricante" = "p"."id_fabricante"
JOIN "frete_regrapromocao" "fr" ON (("fr"."id" = frc.id_regrapromocao) OR ("fr"."id" = frf.id_regrapromocao))
JOIN "frete_regrapromocao_regiao" "frr" ON "frr"."id_regrapromocao" = "fr"."id"
LEFT JOIN "produtos_fretegratis" "pf" ON "pf"."id_produto" = "p"."id"
WHERE "b"."reve_cod" = '1748'
AND "p"."id" = '673884'
AND "fr"."id_formaentrega" = '5408'
AND "fr"."cupom" = 'F'
AND "fr"."status" = 'T'
AND "frr"."esta_cod" = 'PR'
AND "frr"."id_capital" = 'S'
AND "fr"."varejo" = 'T'
AND pf.id ISNULL
AND (p.valor_venda * 1) > "fr"."compra_minima"
GROUP BY "p"."id", "fr"."apelido", "fr"."compra_minima", "fr"."desconto", "fr"."aplicavel_todocarrinho"
ORDER BY "fr"."aplicavel_todocarrinho" DESC, "fr"."desconto" DESC

The problem happens when, on line 10, the value of the store ID is 1748, the query is taking about 5 seconds to execute. I have tested with stores with fewer records in the bank and stores with more records, but the query is executed instantly always, only in this specific case it takes so much.

The database is Postgre and the query is being generated by the Codeigniter Query Builder 3.1. I believe that there is no error in the query, because in other stores works normally.

*The basket table had to be recovered a few days ago, I do not think it is causing the problem, but as I do not understand database, I thought it important to mention...

1

Run a EXPLAIN ANALYZE. https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-ANALYZE. You can also see the tips at https://explain.depesz.com/

– anonimo

2019/02/14 at 21:56
I will test. I managed to get around the situation, but I did not find the problem. I will run a EXPLAIN ANALYZE to find out.

– Jhonny

2019/02/15 at 14:29
It worked, I was able to find where the problem was. This particular store had several records on the table frete_regrapromocao_categorias. Thanks, I didn’t know EXPLAIN ANALYZE

– Jhonny

2019/02/15 at 14:41
It is fundamental for those who work with Postgresql (and also for all other DBMS that implement it).

– anonimo

2019/02/15 at 17:08

1 answer

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.

by Lorenzo Freitas • 46 points · Answer 1 · 2019-02-25T20:44:43+00:00

I recommend you run the queries with pgAdmin3 or pgAdmin4 first, or even through psql. It seems that in one bank it is fast and in another it is slow, it can be some indexing problem.

First if possible run a database backup and restore it again, after running some processes like VACUUM FULL ANALYZE and a REINDEX. Through pgAdmin3 we can run this way:

If these maintenance processes do not result in an improvement, you can run a command to check how the readings are being made in each bank:

EXPLAIN ANALYZE SELECT...

This command returns information from the readings, indexes, cost in milliseconds of each part of your select, etc... You will have to study In pgAdmin via the F7 command there is a visual analysis that can also help.

Any doubts pass the structure of creation of the tables and more information of the version of Postgresql in which these bases are being executed, so we will have more accurate information.