Query extremadamente lenta

[chidomen]

Hola que tal,

Tengo un query que esta demasiado lento, apenas el fin de semana pasado empezamos a notar esto, ya tenemos algo de tiempo con la tabla y el algo grande.

De que forma puedo hacerlo funcionar más rápido?

Este es el query:

SELECT DISTINCT account.first_name AS account_first_name, account.last_name AS account_last_name, account.email AS account_email, flag.*, EXISTS (SELECT * FROM flag_x_trait WHERE flag.id = flag_x_trait.flag_id) AS is_completed FROM account LEFT JOIN flag ON (account.id = flag.account_id) LEFT JOIN account AS buyer ON (buyer.id = flag.buyer_id) WHERE ( LOWER(account.email) LIKE LOWER('%ayala%') OR LOWER(account.first_name) LIKE LOWER('%ayala%') OR LOWER(account.last_name) LIKE LOWER('%ayala%') OR LOWER(buyer.email) LIKE LOWER('%ayala%') OR LOWER(buyer.first_name) LIKE LOWER('%ayala%') OR LOWER(buyer.last_name) LIKE LOWER('%ayala%') OR LOWER(flag.access_code) LIKE LOWER('%ayala%') OR LOWER(flag.first_name) LIKE LOWER('%ayala%') OR LOWER(flag.last_name) LIKE LOWER('%ayala%') OR LOWER(flag.share_email) LIKE LOWER('%ayala%') ) ORDER BY account_id DESC ;

Espero que alguien me pueda orientar

Saludos

[chidomen]

Al analizarlo esto me aparece, no entiendo mucho pero espero que sirva

Unique (cost=1069984.07..1102654.07 rows=726000 width=162) (actual time=68483.715..68484.372 rows=79 loops=1)
-> Sort (cost=1069984.07..1071799.07 rows=726000 width=162) (actual time=68483.693..68483.807 rows=79 loops=1)
Sort Key: flag.account_id, account.first_name, account.last_name, account.email, flag.id, flag.buyer_id, flag.access_code, flag.time_purchased, flag.time_completed, flag.gender, flag.first_name, flag.share_email, flag.generation_description, flag.generation_code, flag.last_name, flag.removed_flag_user, (subplan)
-> Hash Left Join (cost=277770.02..755379.39 rows=726000 width=162) (actual time=47965.673..68479.795 rows=79 loops=1)
Hash Cond: ("outer".buyer_id = "inner".id)
Filter: ((("outer".email)::text ~~* '%ayala%'::text) OR (("outer".first_name)::text ~~* '%ayala%'::text) OR (("outer".last_name)::text ~~* '%ayala%'::text) OR (("inner".email)::text ~~* '%ayala%'::text) OR (("inner".first_name)::text ~~* '%ayala%'::text) OR (("inner".last_name)::text ~~* '%ayala%'::text) OR (("outer".access_code)::text ~~* '%ayala%'::text) OR (("outer".first_name)::text ~~* '%ayala%'::text) OR (("outer".last_name)::text ~~* '%ayala%'::text) OR (("outer".share_email)::text ~~* '%ayala%'::text))
-> Merge Left Join (cost=273420.57..300232.04 rows=726000 width=162) (actual time=43133.858..47034.189 rows=210012 loops=1)
Merge Cond: ("outer".id = "inner".account_id)
-> Index Scan using account_id_idx on account (cost=0.00..15636.38 rows=114036 width=48) (actual time=0.197..688.555 rows=114039 loops=1)
-> Sort (cost=273420.57..275235.57 rows=725999 width=118) (actual time=41191.566..42859.438 rows=726500 loops=1)
Sort Key: flag.account_id
-> Seq Scan on flag (cost=0.00..17315.99 rows=725999 width=118) (actual time=0.041..1637.754 rows=726500 loops=1)
-> Hash (cost=3061.36..3061.36 rows=114036 width=48) (actual time=770.253..770.253 rows=0 loops=1)
-> Seq Scan on account buyer (cost=0.00..3061.36 rows=114036 width=48) (actual time=0.096..477.064 rows=114039 loops=1)
SubPlan
-> Index Scan using flag_x_trait_flag_id_idx on flag_x_trait (cost=0.00..13.09 rows=25 width=16) (actual time=194.010..194.010 rows=0 loops=79)
Index Cond: ($0 = flag_id)
Total runtime: 92844.901 ms

[huesos52]

cuando usas lower en las condiciones, se pierde el uso de los indices que normalente agilizan las consultas.
hacer uso de estas funciones sobre los campos de comparación son bastante costosas para la base de datos,

solo por probar... ejecuta la consulta sin el uso de la función lower con las cadenas de respuesta exacta.

Cuentanos acerca de la diferencia de tiempo

saludos