Thread: unexpected plan for unused table (8.1dev)
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 to_tsquery('new&york&hotel') as query; y; QUERY PLAN --------------------------------------------------------------------------------------------------------------- FunctionScan on query (cost=0.00..15.00 rows=1000 width=32) (actual time=3801.069..3801.071 rows=1 loops=1) Total runtime:3801.107 ms (2 rows) 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; QUERY PLAN --------------------------------------------------------------------------- Nested Loop (cost=13.50..44280620.90 rows=1964340000width=32) -> Seq Scan on place (cost=0.00..82957.40 rows=1964340 width=0) -> Materialize (cost=13.50..23.50rows=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. 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
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