Tom Lane skrev:
Fredrik Olsson <fredrik.olsson@xxxxxxxxx> 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@xxxxxxxxx
