Date: Thu, 4 Oct 2007 08:30:38 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Henrik <henke@mac.se>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain
	output.
Message-ID: <20071004123038.GA6176@alvh.no-ip.org>
References: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
	<15793.1191420918@sss.pgh.pa.us>
	<2E2D02AB-123A-474D-A197-91A61DC64570@mac.se>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2E2D02AB-123A-474D-A197-91A61DC64570@mac.se>
User-Agent: Mutt/1.5.16 (2007-06-11)

Henrik wrote:

> Ahh I had exactly 8 joins.
> Following your suggestion I raised the join_collapse_limit from 8 to 10 and 
> the planners decision sure changed but now I have some crazy nested loops. 
> Maybe I have some statistics wrong?

Yeah.  The problematic misestimation is exactly the innermost indexscan,
which is wrong by two orders of magnitude:

>                                                    ->  Index Scan using 
> tbl_file_idx on tbl_file  (cost=0.01..8.66 rows=1 width=39) (actual 
> time=0.057..931.546 rows=2223 loops=1)
>                                                          Index Cond: 
> ((lower((file_name)::text) ~>=~ 'index.php'::character varying) AND 
> (lower((file_name)::text) ~<~ 'index.phq'::character varying))
>                                                          Filter: 
> (lower((file_name)::text) ~~ 'index.php%'::text)

This wreaks havoc on the rest of the plan.  If this weren't
misestimated, it wouldn't be using those nested loops.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support