pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedinefficient query plan with left joined view
4+ messages / 2 participants
[nested] [flat]
* inefficient query plan with left joined view
@ 2003-01-28 16:50 Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 18:30 ` Re: inefficient query plan with left joined view Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Pflug @ 2003-01-28 16:50 UTC (permalink / raw)
To: pgsql-performance
What I'm doing on V7.3.1:
select t1.keycol, t2.keycol
from tab1 t1
LEFT join myview t2 on t1.keycol=t2.keycol
where t1.keycol=1000001
t1 has 100 rows, t2 has 4000, both with keycol as PK.
the view is created as
CREATE myview AS SELECT keycol, 22::integer as calc_col FROM tab2
The query plan will show an ugly subquery scan on all tab2 rows. If the
view is created without calculated columns, the query plan looks as
expected showing and index scan on tab2 with the correct condition,
inner join will always be ok.
In real life, the view consists of a lot more tables, and the tables may
contain >1,000,000 rows so you may imagine the performance...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inefficient query plan with left joined view
2003-01-28 16:50 inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
@ 2003-01-28 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-28 19:31 ` Re: inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-01-28 18:30 UTC (permalink / raw)
To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-performance
Andreas Pflug <Andreas.Pflug@web.de> writes:
> What I'm doing on V7.3.1:
> select t1.keycol, t2.keycol
> from tab1 t1
> LEFT join myview t2 on t1.keycol=t2.keycol
> where t1.keycol=1000001
> the view is created as
> CREATE myview AS SELECT keycol, 22::integer as calc_col FROM tab2
The subquery isn't pulled up because it doesn't pass the
has_nullable_targetlist test in src/backend/optimizer/plan/planner.c.
If we did flatten it, then references to calc_col wouldn't correctly
go to NULL when the LEFT JOIN should make them do so --- they'd be
22 all the time.
As the notes in that routine say, it could be made smarter: strict
functions of nullable variables could be allowed. So if your real
concern is not '22' but something like 'othercol + 22' then this is
fixable.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inefficient query plan with left joined view
2003-01-28 16:50 inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 18:30 ` Re: inefficient query plan with left joined view Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-28 19:31 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 22:41 ` Re: inefficient query plan with left joined view Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Pflug @ 2003-01-28 19:31 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
>The subquery isn't pulled up because it doesn't pass the
>has_nullable_targetlist test in src/backend/optimizer/plan/planner.c.
>If we did flatten it, then references to calc_col wouldn't correctly
>go to NULL when the LEFT JOIN should make them do so --- they'd be
>22 all the time.
>
>As the notes in that routine say, it could be made smarter: strict
>functions of nullable variables could be allowed. So if your real
>concern is not '22' but something like 'othercol + 22' then this is
>fixable.
>
> regards, tom lane
>
>
>
Tom,
actually my views do use calculated columns (mostly concated strings,
e.g. full name from title/1st/last name). As the example shows even
columns that are never used will be taken into account when checking
has_nullable_targetlist. Unfortunately I have a lot of views containing
views which containing.... delivering a lot more columns than needed.
But they are checked anyway...
I'd expect the parser to look at the join construction only to find out
about available data. Why should the selected (and even unselected)
columns be evaluated if the join delivers no result? Maybe this can be
achieved by checking only JOIN ON/WHERE columns with
has_nullable_targetlist?
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inefficient query plan with left joined view
2003-01-28 16:50 inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 18:30 ` Re: inefficient query plan with left joined view Tom Lane <tgl@sss.pgh.pa.us>
2003-01-28 19:31 ` Re: inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
@ 2003-01-28 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-01-28 22:41 UTC (permalink / raw)
To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-performance
Andreas Pflug <Andreas.Pflug@web.de> writes:
> As the example shows even
> columns that are never used will be taken into account when checking
> has_nullable_targetlist.
It's not really practical to do otherwise, as the code that needs to
check this doesn't have access to a list of the columns actually used.
Even if we kluged things up enough to make it possible to find that out,
that would merely mean that *some* of your queries wouldn't have a
problem.
What about improving the intelligence of the nullability check --- or do
you have non-strict expressions in there?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-01-28 22:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-28 16:50 inefficient query plan with left joined view Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-28 19:31 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-01-28 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox