From: Michael Fuhr <mike@fuhr.org>
To: Henrik <henke@mac.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain output.
Date: Wed, 3 Oct 2007 07:31:29 -0600
Message-ID: <20071003133128.GA62692@winnie.fuhr.org> (raw)
In-Reply-To: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
References: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
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
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: mike@fuhr.org, henke@mac.se
Subject: Re: Query taking too long. Problem reading explain output.
In-Reply-To: <20071003133128.GA62692@winnie.fuhr.org>
* 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