Slows Querys
Posted: 11 Oct 2022, 16:22
good afternoon, I am writing to make you a query to see if you had any problems with these selects.
All of them are taking me approximately 7 seconds to run which is generating a slowness in the users of the mantis.
The queries are as follows:
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) )
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.date_submitted DESC, mantis_bug_table.last_updated DESC LIMIT ? OFFSET ?
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.sticky DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?
attach explain plain
thanks a lot!!!
All of them are taking me approximately 7 seconds to run which is generating a slowness in the users of the mantis.
The queries are as follows:
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) )
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.date_submitted DESC, mantis_bug_table.last_updated DESC LIMIT ? OFFSET ?
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.sticky DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?
SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?
attach explain plain
thanks a lot!!!