Re: how to control the execution plan ?

Hi Scott,

I add the answers below.

>
> Just wondering what the query plans look like here, both regular
> explain, and if you can wait for it to execute, explain analyze.
>

Just with explain, because the function craches when it is running:

"Merge Join (cost=141.41..188.32 rows=1 width=24)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
" Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
" -> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
" -> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=79.08..79.09 rows=1 width=28)"
" Sort Key: bp."COL_C""
" -> Hash Join (cost=10.59..79.07 rows=1 width=28)"
" Hash Cond: (bp."COL_B" = pn."PK_ID")"
" -> Seq Scan on "TABLE_A" bp (cost=0.00..68.46 rows=4
width=32)"
" Filter: (("COL_A" IS NOT NULL) AND (("COL_A")::text <>
''::text) AND ((("MY_FUNCTION_A"("COL_A", NULL::boolean))::text || '
'::text) IS NULL))"
" -> Hash (cost=10.50..10.50 rows=7 width=4)"
" -> Seq Scan on "TABLE_B" pn (cost=0.00..10.50 rows=7
width=4)"
" Filter: (("COL_E")::text ~~ 'Some%'::text)"

> I'm guessing that the function is not indexed / indexable. Is it
> marked immutable (and is it actually immutable) or stable (and is
> stable)?
>

The function is marked stable.

> If it's still to smart, you can run two queries, one to pull the set
> you want to work with from the custom function into a temp table, then
> analyze it, then run the query against that.
> Not an optimal solution, but it might be the fastest if you can't
> index your function.
>

In fact I would use that statement to define a permanent view, not in a
procedure.

Finally I found a trick specifying not just WHERE (x.ALIAS_A::text ) IS
NULL;, but combining with a constant and a join with a constant.
By the way, it doesn't works just with the constant or with the join :(
See the query and the plan below:

EXPLAIN SELECT *
FROM (
SELECT "MY_FUNCTION_A"(bp."COL_A", NULL::boolean) AS ALIAS_A, MY_AUX
FROM "TABLE_A" bp
CROSS JOIN (
SELECT '*'::character varying AS MY_AUX
) afp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."COL_E"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL
AND bp."COL_A"::text <> ''::text
) x
WHERE (x.ALIAS_A::text || ' ' || MY_AUX) IS NULL;

"Merge Join (cost=131.68..178.60 rows=1 width=56)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
" Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
" -> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
" -> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=69.36..69.36 rows=1 width=60)"
" Sort Key: bp."COL_C""
" -> Nested Loop (cost=10.59..69.34 rows=1 width=60)"
" Join Filter: (((("MY_FUNCTION_A"(bp."COL_A",
NULL::boolean))::text || ' '::text) || (afp.MY_AUX)::text) IS NULL)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Hash Join (cost=10.59..68.94 rows=22 width=28)"
" Hash Cond: (bp."COL_B" = pn."PK_ID")"
" -> Seq Scan on "TABLE_A" bp (cost=0.00..54.90
rows=862 width=32)"
" Filter: (("COL_A" IS NOT NULL) AND
(("COL_A")::text <> ''::text))"
" -> Hash (cost=10.50..10.50 rows=7 width=4)"
" -> Seq Scan on "TABLE_B" pn (cost=0.00..10.50
rows=7 width=4)"
" Filter: (("COL_E")::text ~~ 'Some%'::text)"

However I'm not sure there are no circumstances when the execution plan will
detect my trick and will optimize the query again :((

Sabin