unexpected plan for unused table (8.1dev)

On E, 2005-08-29 at 09:50 +0400, Oleg Bartunov wrote:

Hi there,

I'm a bit surprised how bad could be plan for unused table.
I had to cancel second query :)

...

tp=# explain analyze select tp_rewrite_substitute(query,
'select p.name_tsquery, p.name_alias_tsquery from place p')
from place, to_tsquery('new&york&hotel') as query;
^^^^^

Here is plan without analyze:

tp=# explain select tp_rewrite_substitute(query,
'select p.name_tsquery, p.name_alias_tsquery from place p')
from place, to_tsquery('new&york&hotel') as query;

Actually you are asking the query to be evaluated once for each row of
table "place".

QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (cost=13.50..44280620.90 rows=1964340000 width=32)
-> Seq Scan on place (cost=0.00..82957.40 rows=1964340 width=0)
-> Materialize (cost=13.50..23.50 rows=1000 width=32)
-> Function Scan on query (cost=0.00..12.50 rows=1000 width=32)
(4 rows)

The only difference is unused table 'place' in FROM-clause. I typed in by
mistake, but I think optimizer could figure out not to take into account
this table.

This is valid SQL and afaik exactly what you asked for in the query.
Postgresql can't possibly know that you made a mistake :)

--
Hannu Krosing <hannu@skype.net>

On Mon, 29 Aug 2005, Hannu Krosing wrote:

The only difference is unused table 'place' in FROM-clause. I typed in by
mistake, but I think optimizer could figure out not to take into account
this table.

This is valid SQL and afaik exactly what you asked for in the query.
Postgresql can't possibly know that you made a mistake :)

Oh, yes, you're right, of course !

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83