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

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