From: Fredrik Olsson <fredrik.olsson@treyst.se>
To: pgsql-performance@postgresql.org
Subject: Re: Force another plan.
Date: Mon, 20 Feb 2006 10:58:41 +0100
Message-ID: <43F992D1.6040308@treyst.se> (raw)
In-Reply-To: <11673.1140370277@sss.pgh.pa.us>
References: <43F74592.7010701@treyst.se>
<20490.1140287811@sss.pgh.pa.us>
<43F866E2.20001@treyst.se>
<11673.1140370277@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fredrik.olsson@treyst.se
Subject: Re: Force another plan.
In-Reply-To: <43F992D1.6040308@treyst.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox