From: Alvaro Herrera <alvherre@commandprompt.com>
To: Henrik <henke@mac.se>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain output.
Date: Thu, 4 Oct 2007 08:30:38 -0400
Message-ID: <20071004123038.GA6176@alvh.no-ip.org> (raw)
In-Reply-To: <2E2D02AB-123A-474D-A197-91A61DC64570@mac.se>
References: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
<15793.1191420918@sss.pgh.pa.us>
<2E2D02AB-123A-474D-A197-91A61DC64570@mac.se>
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
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: alvherre@commandprompt.com, henke@mac.se, tgl@sss.pgh.pa.us
Subject: Re: Query taking too long. Problem reading explain output.
In-Reply-To: <20071004123038.GA6176@alvh.no-ip.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