Re: Extreme slow select query 8.2.4

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Extreme slow select query 8.2.4
Дата
Msg-id 27459.1186412327@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Extreme slow select query 8.2.4  (Henrik Zagerholm <henke@mac.se>)
Ответы Re: Extreme slow select query 8.2.4  (Henrik Zagerholm <henke@mac.se>)
Список pgsql-performance
Henrik Zagerholm <henke@mac.se> writes:
> ... FROM tbl_file_structure
>           JOIN tbl_file ON pk_file_id = fk_file_id
>           JOIN tbl_structure ON pk_structure_id = fk_structure_id
>           JOIN tbl_archive ON pk_archive_id =     fk_archive_id
>           JOIN tbl_share ON pk_share_id = fk_share_id
>           JOIN tbl_computer ON pk_computer_id = fk_computer_id
>           JOIN tbl_filetype ON pk_filetype_id = fk_filetype_id
>           JOIN tbl_acl ON fk_file_structure_id = pk_file_structure_id
>           LEFT OUTER JOIN tbl_job ON tbl_archive.fk_job_id = pk_job_id
>           LEFT OUTER JOIN tbl_job_group ON tbl_job.fk_job_group_id =
> pk_job_group_id
>            WHERE LOWER(file_name) LIKE LOWER('awstats%') AND
> archive_complete = true  AND job_group_type != 'R' GROUP BY
> file_name, file_ctime, structure_path, pk_computer_id, filetype_icon, computer_name,
> share_name, share_path ...

Perhaps raising join_collapse_limit and/or work_mem would help.
Although I'm not really sure why you expect the above query to be fast
--- with the file_name condition matching 50K rows, and no selectivity
worth mentioning in any other WHERE-condition, it's gonna have to do a
heck of a lot of joining in any case.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Henrik Zagerholm
Дата:
Сообщение: Re: Planner making wrong decisions 8.2.4. Insane cost calculations.
Следующее
От: Gregory Stark
Дата:
Сообщение: Re: Planner making wrong decisions 8.2.4. Insane cost calculations.