Very time consuming query depending on query ID

Question

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

Answer 1

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.