Planner weakness (was: Re: ExecEvalExpr: unknown expression type 108) - Mailing list pgsql-general

Dear Gurus,

I think I have something to aid you (a probable bug). Please read the
abstract at least and decide whether to continue with the "In Short" part.

If it's not clear enough (I stripped as much as possible to avoid being
lost) I'll take the pain and try to reproduce the phenomenon in a smaller,
working example and send a dump to those willing to help.

ABSTRACT:
1. I managed to reproduce the error I mentioned in a former mail, and it's
based on different forms of the same query (subselect or join).
??? Is it normal for a subselect not to work in a query where a join (that
produces the same result) works?

2. I managed to create a fast and exactly working query but I think I did
some of the job the planner would have.
??? Is it common when developing in PostgreSQL, or is it considered a
"to-be-improved" thing or even a bug?

IN SHORT:
#1. the planner seems to be unable to optimize functions in view
definitions.
#2. putting the subquery in the view def and then calling a subselect on it
causes an "ExecEvalExpr: unknown expression type 108"
#3. Commenting the subselect line works but I need that field :)
#4. joining instead of subselect works good, but
#5. if the join is done with a subquery, no matter how trivial, maddens the
planner, as in "549.2msec vs 9.5msec"

DETAILS:
Here are my original queries (stripped of things I thought irrelevant) and
their explain analyzes. First is #2, throwing error. Second is #4, third is
#5. See the relevant part of the view's structure in my previous mail,
included at the end of this mail.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
-- #2. This throws an error:
tir=> (SELECT
tir(>     (SELECT az_jel from x where az=t.x) as x_az_jel
tir(> FROM t_item t
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
ERROR: ExecEvalExpr: unknown expression type 108
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
-- #4. The faster solution. This requires relation prefixes to all fields in
-- SELECT, WHERE and ORDER BY since most are common in
-- view t and table x:

tir=> (SELECT
tir(>     x.az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>      x ON (x.az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
 az_jel
--------

(1 row)
tir=> explain analyze (SELECT
tir(>     x.az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>      x ON (x.az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY t.az_jel, t.teljesites;
NOTICE:  QUERY PLAN:

Sort  (cost=82.26..82.26 rows=1 width=66) (actual time=8.43..8.43 rows=1
loops=1)
  ->  Nested Loop  (cost=0.00..82.25 rows=1 width=66) (actual
time=3.75..8.02 rows=1 loops=1)
        ->  Nested Loop  (cost=0.00..66.11 rows=1 width=50) (actual
time=3.23..7.50 rows=1 loops=1)
              ->  Nested Loop  (cost=0.00..50.07 rows=1 width=33) (actual
time=2.02..5.14 rows=28 loops=1)
                    ->  Index Scan using szlltlvl_tljsts on szallitolevel b
(cost=0.00..43.68 rows=1 width=19) (actual time=0.16..1.49 rows=17 loops=1)
                    ->  Index Scan using szallitolevel_modositasa_pkey on
szallitolevel_modositasa m  (cost=0.00..6.38 rows=1 width=14) (actual
time=0.16..0.18 rows=2 loops=17)
              ->  Index Scan using szallitolevel_szallitolevel_key on
szallitolevel_tetele t  (cost=0.00..14.50 rows=1 width=17) (actual
time=0.07..0.07 rows=0 loops=28)
        ->  Index Scan using x_az_key on x  (cost=0.00..10.86 rows=1
width=16) (actual time=0.01..0.01 rows=0 loops=1)
              SubPlan
                ->  Limit  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.46..0.46 rows=0 loops=1)
                      ->  Sort  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.45..0.45 rows=0 loops=1)
                            ->  Index Scan using szlltlvl_szmlzs_szlltlvl on
t_x i  (cost=0.00..5.26 rows=1 width=12) (actual time=0.06..0.06 rows=0
loops=1)
                ->  Limit  (cost=5.27..5.27 rows=1 width=12)
                      ->  Sort  (cost=5.27..5.27 rows=1 width=12)
                            ->  Index Scan using szlltlvl_szmlzs_szlltlvl on
t_x i  (cost=0.00..5.26 rows=1 width=12)
Total runtime: 9.47 msec

---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
---------------------------- cut here ------------------------------
-- #5. The slower solution. This doesn't require relation prefixes
-- since the subquery in the join does the job. Not a big deal.
-- However, it maddens the planner:

tir=> (SELECT
tir(>     sz_az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>     (select az as sz_az, az_jel as sz_az_jel from x) as x
           ON (x.sz_az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY az_jel, teljesites;
 sz_az_jel
-----------

(1 row)

tir=> explain analyze (SELECT
tir(>     sz_az_jel
tir(> FROM t_item t LEFT OUTER JOIN
tir(>     (select az as sz_az, az_jel as sz_az_jel from x) as x
          ON (x.sz_az=t.x)
tir(> WHERE -- snip...
tir-> ORDER BY az_jel, teljesites;
NOTICE:  QUERY PLAN:

Sort  (cost=6857.66..6857.66 rows=6 width=122) (actual time=548.34..548.34
rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..6857.58 rows=6 width=122) (actual
time=543.59..547.96 rows=1 loops=1)
        ->  Nested Loop  (cost=0.00..66.11 rows=1 width=50) (actual
time=3.19..7.55 rows=1 loops=1)
              ->  Nested Loop  (cost=0.00..50.07 rows=1 width=33) (actual
time=2.00..5.21 rows=28 loops=1)
                    ->  Index Scan using szlltlvl_tljsts on szallitolevel b
(cost=0.00..43.68 rows=1 width=19) (actual time=0.15..1.52 rows=17 loops=1)
                    ->  Index Scan using szallitolevel_modositasa_pkey on
szallitolevel_modositasa m  (cost=0.00..6.38 rows=1 width=14) (actual
time=0.16..0.18 rows=2 loops=17)
              ->  Index Scan using szallitolevel_szallitolevel_key on
szallitolevel_tetele t  (cost=0.00..14.50 rows=1 width=17) (actual
time=0.07..0.07 rows=0 loops=28)
        ->  Subquery Scan x  (cost=0.00..54.75 rows=1275 width=16) (actual
time=0.03..35.83 rows=1275 loops=1)
              ->  Seq Scan on x  (cost=0.00..54.75 rows=1275 width=16)
(actual time=0.02..25.22 rows=1275 loops=1)
        SubPlan
          ->  Limit  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.38..0.38 rows=0 loops=1275)
                ->  Sort  (cost=5.27..5.27 rows=1 width=12) (actual
time=0.37..0.37 rows=0 loops=1275)
                      ->  Index Scan using szlltlvl_szmlzs_szlltlvl on t_x i
(cost=0.00..5.26 rows=1 width=12) (actual time=0.05..0.05 rows=0 loops=1275)
Total runtime: 549.20 msec

EXPLAIN
---------------------------- cut here ------------------------------


----- Original Message -----
From: "SZŰCS Gábor" <surrano@mailbox.hu>
Sent: Wednesday, December 11, 2002 4:25 PM
Subject: Re: [GENERAL] ExecEvalExpr: unknown expression type 108


> Thanks Krisztian,
>
> I checked google and most of the pgsql archive messages it pointed to, but
I
> found that this message is commonly related to subselects in table
> constraints.
>
> If I didn't misunderstand it, Tom once titled this as "probably solved in
> 6.5" or such, back in 1999.
>
> However, my problem is different, and even if it's not vital now, I'd like
> to get some suggestions where to search if the problem persists.
>
> I use 7.2.1 and met the problem BUT ONCE, after changing a field
definition
> in a view from a function call to the explicit content of the function,
like
> this:
>
>   -- t and x are business files,
>   -- with items referring to t in t_item
>   -- and their N:M relations with x and x_item (not important now) in t_x.
>   -- I want to select the latest x.id from the relations table:
>   --
>   -- myfunc(t.id, t_item.no) AS x, -- this was the original
>   (SELECT x FROM t_x
>    WHERE t_x.t = t.id AND t_x.t_item = t_item.no
>    ORDER BY tstamp DESC LIMIT 1
>   ) AS x,
>
> The error message occured after I recreated the view (and its rule), but
> when I tried to reproduce it to send a usable mail, it evilly refused to
> fail and works correctly ever since ;) I fear for the worst, that the
> problem comes to life again when it goes from test to live use.
>
> Well, even if it comes to life, I can get back to calling that function;
the
> problem is, that if I wish to JOIN with table x using this field of the
> view, the planner makes a seq scan on table x, unlike when I write the
> function body in the view definition.
>
> Since one produces 17sec and the other 0.11sec as total time for the same
> query, I think the planner doesn't deliberately choose seq scan; it can't
> choose index scan. So, I helped it to see what I want in depth, and that
> caused the error -- I repeat, it caused the error but once.