From: Tom Lane <tgl@sss.pgh.pa.us>
To: Henrik <henke@mac.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain output.
Date: Wed, 03 Oct 2007 10:15:18 -0400
Message-ID: <15793.1191420918@sss.pgh.pa.us> (raw)
In-Reply-To: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
References: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
Henrik <henke@mac.se> writes:
> Here is the query if needed.
> explain analyze SELECT file_name 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 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('index.php%') AND (computer_name = 'SOLARIS2') AND
> (fk_archive_id = 56) AND archive_complete = true AND (job_group_type
> = 'B' OR job_group_type IS NULL) GROUP BY file_name, file_ctime,
> structure_path, pk_computer_id, filetype_icon, computer_name,
> share_name, share_path;
[ counts the JOINs... ] Try raising join_collapse_limit. I think the
planner may be neglecting to consider the join order you need.
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: Query taking too long. Problem reading explain output.
In-Reply-To: <15793.1191420918@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