Re: Query taking too long. Problem reading explain output.

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