From: | Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)se> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Force another plan. |
Date: | 2006-02-20 09:58:41 |
Message-ID: | 43F992D1.6040308@treyst.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane skrev:
> Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)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(dot)olsson(at)treyst(dot)se
From | Date | Subject | |
---|---|---|---|
Next Message | Marcos | 2006-02-20 10:50:36 | Creating a correct and real benchmark |
Previous Message | Virag Saksena | 2006-02-20 06:42:23 | Re: Avoiding cartesian product |