From: Tom Lane <tgl@sss.pgh.pa.us>
To: Henrik Zagerholm <henke@mac.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Extreme slow select query 8.2.4
Date: Mon, 06 Aug 2007 10:58:47 -0400
Message-ID: <27459.1186412327@sss.pgh.pa.us> (raw)
In-Reply-To: <B1D089D3-5CB8-45F7-93EA-A81A492A543E@mac.se>
References: <B1D089D3-5CB8-45F7-93EA-A81A492A543E@mac.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, henke@mac.se
Subject: Re: Extreme slow select query 8.2.4
In-Reply-To: <27459.1186412327@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox