Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D05A09F9DB1 for ; Wed, 3 Oct 2007 10:31:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 25955-05 for ; Wed, 3 Oct 2007 10:31:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 850149F9D8E for ; Wed, 3 Oct 2007 10:31:50 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l93DVUme055695 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 3 Oct 2007 07:31:33 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.14.1/8.14.1) with ESMTP id l93DVUFA062813; Wed, 3 Oct 2007 07:31:30 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.14.1/8.14.1/Submit) id l93DVT4c062812; Wed, 3 Oct 2007 07:31:29 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 3 Oct 2007 07:31:29 -0600 From: Michael Fuhr To: Henrik Cc: pgsql-performance@postgresql.org Subject: Re: Query taking too long. Problem reading explain output. Message-ID: <20071003133128.GA62692@winnie.fuhr.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/21 X-Sequence-Number: 27043 On Wed, Oct 03, 2007 at 10:03:53AM +0200, Henrik wrote: > I have a little query that takes too long and what I can see in the > explain output is a seq scan on my biggest table ( tbl_file_structure) > which I can't explain why. Here's where almost all of the time is taken: > Hash Join (cost=8605.68..410913.87 rows=19028 width=40) (actual time=22.810..16196.414 rows=17926 loops=1) > Hash Cond: (tbl_file_structure.fk_file_id = tbl_file.pk_file_id) > -> Seq Scan on tbl_file_structure (cost=0.00..319157.94 rows=16591994 width=16) (actual time=0.016..7979.083 rows=16591994 loops=1) > -> Hash (cost=8573.62..8573.62 rows=2565 width=40) (actual time=22.529..22.529 rows=2221 loops=1) > -> Bitmap Heap Scan on tbl_file (cost=74.93..8573.62 rows=2565 width=40) (actual time=1.597..20.691 rows=2221 loops=1) > Filter: (lower((file_name)::text) ~~ 'index.php%'::text) > -> Bitmap Index Scan on tbl_file_idx (cost=0.00..74.28 rows=2565 width=0) (actual time=1.118..1.118 rows=2221 loops=1) > Index Cond: ((lower((file_name)::text) ~>=~ 'index.php'::character varying) AND (lower((file_name)::text) ~<~ 'index.phq'::character varying)) Does tbl_file_structure have an index on fk_file_id? If so then what's the EXPLAIN ANALYZE output if you set enable_seqscan to off? I don't recommend disabling sequential scans permanently but doing so can be useful when investigating why the planner thinks one plan will be faster than another. What are your settings for random_page_cost, effective_cache_size, work_mem, and shared_buffers? If you're using the default random_page_cost of 4 then what's the EXPLAIN ANALYZE output if you reduce it to 3 or 2 (after setting enable_seqscan back to on)? -- Michael Fuhr