Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 403589F98FF for ; Wed, 3 Oct 2007 11:15:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 66079-09 for ; Wed, 3 Oct 2007 11:15:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 21F579F9A39 for ; Wed, 3 Oct 2007 11:15:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l93EFIVg015794; Wed, 3 Oct 2007 10:15:18 -0400 (EDT) To: Henrik cc: pgsql-performance@postgresql.org Subject: Re: Query taking too long. Problem reading explain output. In-reply-to: References: Comments: In-reply-to Henrik message dated "Wed, 03 Oct 2007 10:03:53 +0200" Date: Wed, 03 Oct 2007 10:15:18 -0400 Message-ID: <15793.1191420918@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/22 X-Sequence-Number: 27044 Henrik 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