From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Henrik <henke(at)mac(dot)se> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query taking too long. Problem reading explain output. |
Date: | 2007-10-04 12:30:38 |
Message-ID: | 20071004123038.GA6176@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Trutwin | 2007-10-04 15:28:04 | Tuning Help - What did I do wrong? |
Previous Message | Richard Huxton | 2007-10-04 10:34:29 | Re: can't shrink relation |