From: | "Sabin Coanda" <sabin(dot)coanda(at)deuromedia(dot)ro> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: how to control the execution plan ? |
Date: | 2008-07-08 16:37:41 |
Message-ID: | g5054i$17fv$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marcin Krawczyk | 2008-07-08 18:44:58 | Re: exception handling and CONTINUE |
Previous Message | Pavel Stehule | 2008-07-08 13:13:05 | Re: exception handling and CONTINUE |