Re: Force another plan.

Tom Lane skrev:
> Fredrik Olsson <fredrik.olsson@treyst.se> writes:
>
>>         ->  Seq Scan on t_entities  (cost=0.00..1.49 rows=7 width=4)
>> (actual time=404.539..409.302 rows=2 loops=1)
>>               Filter: ((haveaccess(createdby, responsible, "class",
>> false) OR CASE WHEN (partof = 'contacts'::name) THEN
>> ischildof(ancestorof(me()), "ID") ELSE false END) AND (subplan))
>>               SubPlan
>>                 ->  Function Scan on alleventoccurances
>> (cost=0.00..12.50 rows=1000 width=8) (actual time=27.871..27.871 rows=0
>> loops=14)
>>
>
> This seems to be your problem right here: evaluating that subplan for
> each row of t_entities is pretty expensive, and yet the planner's
> estimating a total cost of only 1.49 to run the scan.  What PG version
> is this?  AFAICT we've accounted for subplan costs in scan quals for
> a long time, certainly since 7.4.  Can you put together a self-contained
> test case for this?
>
>
I have found the real bottle-neck, looks like I trusted PG to do a bit
too much magic. Since all table accesses go through views, and the first
11 columns always look the same, I did a "proxy-view" to simplify my
"real-views" to:
SELECT pv.*, ...
The "proxy-view" fetches from t_entities, and so does the "real-views"
to do a filter on "partof". This resulted in two scans on  t_entities
when only one is needed. Skipping the "proxy-view" allows PG to chose
the best plan for every case. I guess I stressed the optimizer a bit too
far :).

Is a self contained test-case for the old way with the "proxy-view" is
still wanted?

--
//Fredrik Olsson
  Treyst AB
  +46-(0)19-362182
  fredrik.olsson@treyst.se