Re: Query becomes slow when written as view

On Thu, Feb 14, 2013 at 7:23 AM, Jan Strube <js@deriva.de> wrote:
> Hi,
>
> I have the following query which runs reasonably fast under PostgreSQL
> 9.1.8:
>
> SELECT
> b."ISIN",
> CASE
> WHEN b."COMMENT" IS NOT NULL THEN b."COMMENT"
> WHEN cc."ISIN" IS NOT NULL THEN cc.comment
> ELSE get_comment(b."ISIN")
> END AS "COMMENT"
> FROM dtng."Z_BASE" b
> LEFT JOIN dtng.cached_comments cc on b."ISIN" =3D cc."ISIN" AND cc.cache_=
time
>>=3D b._last_modified
> WHERE b."ISIN" IN (SELECT "ISIN" from dtng."Z_BASE" LIMIT 1)
>
> Here is the query plan:
>
> Nested Loop Left Join (cost=3D0.08..16.65 rows=3D1 width=3D1053)
> Join Filter: (cc.cache_time >=3D b._last_modified)
> -> Nested Loop (cost=3D0.08..8.67 rows=3D1 width=3D644)
> -> HashAggregate (cost=3D0.08..0.09 rows=3D1 width=3D13)
> -> Subquery Scan on "ANY_subquery" (cost=3D0.00..0.08 rows=3D1 width=3D13=
)
> -> Limit (cost=3D0.00..0.07 rows=3D1 width=3D13)
> -> Seq Scan on "Z_BASE" (cost=3D0.00..106515.68 rows=3D1637368 width=3D13=
)
> -> Index Scan using "Z_BASE_pkey" on "Z_BASE" b (cost=3D0.00..8.57 rows=
=3D1
> width=3D644)
> Index Cond: (("ISIN")::bpchar =3D ("ANY_subquery"."ISIN")::bpchar)
> -> Index Scan using cached_comments_pkey on cached_comments cc
> (cost=3D0.00..7.71 rows=3D1 width=3D425)
> Index Cond: ((b."ISIN")::bpchar =3D ("ISIN")::bpchar)
>
> When I=B4m trying to put this into a view, it becomes extremely slow:
>
> CREATE VIEW export_comments AS
> SELECT
> b."ISIN",
> CASE
> WHEN b."COMMENT" IS NOT NULL THEN b."COMMENT"
> WHEN cc."ISIN" IS NOT NULL THEN cc.comment
> ELSE get_comment(b."ISIN")
> END AS "COMMENT"
> FROM dtng."Z_BASE" b
> LEFT JOIN dtng.cached_comments cc on b."ISIN" =3D cc."ISIN" AND cc.cache_=
time
>>=3D b._last_modified
>
> SELECT *
> FROM export_comments
> WHERE "ISIN" IN (SELECT "ISIN" FROM dtng."Z_BASE" LIMIT 1)
>
> The query plan now is:
>
> Hash Join (cost=3D79926.52..906644.87 rows=3D818684 width=3D45)
> Hash Cond: ((b."ISIN")::bpchar =3D ("ANY_subquery"."ISIN")::bpchar)
> -> Hash Left Join (cost=3D79926.42..884049.08 rows=3D1637368 width=3D1053=
)
> Hash Cond: ((b."ISIN")::bpchar =3D (cc."ISIN")::bpchar)
> Join Filter: (cc.cache_time >=3D b._last_modified)
> -> Seq Scan on "Z_BASE" b (cost=3D0.00..106515.68 rows=3D1637368 width=3D=
644)
> -> Hash (cost=3D74620.41..74620.41 rows=3D77841 width=3D425)
> -> Seq Scan on cached_comments cc (cost=3D0.00..74620.41 rows=3D77841 wid=
th=3D425)
> -> Hash (cost=3D0.09..0.09 rows=3D1 width=3D13)
> -> HashAggregate (cost=3D0.08..0.09 rows=3D1 width=3D13)
> -> Subquery Scan on "ANY_subquery" (cost=3D0.00..0.08 rows=3D1 width=3D13=
)
> -> Limit (cost=3D0.00..0.07 rows=3D1 width=3D13)
> -> Seq Scan on "Z_BASE" (cost=3D0.00..106515.68 rows=3D1637368 width=3D13=
)
>
> By the way I get the same behaviour and query plan when I try this:
>
> SELECT *
> FROM (
> -- above view definition
> ) x
> WHERE x."ISIN" IN (SELECT "ISIN" FROM dtng."Z_BASE" LIMIT 1)
>
> We already found out that the problem is the Perl function "get_comment"
> which is very expensive. In the first case the function is called at most
> once, but in the second case it is called many times. I believe this is
> because of the hash join which causes the view to fetch everything from
> dtng."Z_BASE" first?
> The question is, how to avoid this? We tried to set the functions cost fr=
om
> 100 to 10000000 but that did not help. (Because of the architecture of th=
e
> software that uses this query, we have the constraint that structure of t=
he
> final WHERE clause (WHERE "ISIN" IN (...)) must not be altered.)
>
> Thanks a lot for any idea,
> Jan

is your function stable/immutable, and if so is it decorated as such.

merlin