Thread: Wacky query plan, why?
I have 2 queries, one is never returns, like explain shows 677195846.00
cost
and another doing the same job works ( cost 6072.00 )
I do not understand one thing, why query number one, generates so
unbelievably
screwed up plan?
why it does not use index?
query # 1:
------------------------------------------------------------------------
------
explain update prod.t_results set expdate=e.termdate from
work.termdate e, prod.t_results r where e.docid=r.docid;
or update prod.t_results set expdate=e.termdate from
work.termdate e
INNER JOIN prod.t_results r on
(e.docid=r.docid);
Nested Loop (cost=0.00..677195846.00 rows=19269540000 width=138)
-> Nested Loop (cost=0.00..3046.00 rows=1000 width=16)
-> Seq Scan on termdate e (cost=0.00..20.00 rows=1000
width=12)
-> Index Scan using t_resultsid on t_results r
(cost=0.00..3.01 rows=1 width=4)
Index Cond: ("outer".docid = r.docid)
-> Seq Scan on t_results (cost=0.00..484497.40 rows=19269540
width=122)
query # 2:
------------------------------------------------------------------------
------
explain update prod.t_results set expdate=e.termdate from
(select r.docid, t.termdate from work.termdate t,
prod.t_results r
where t.docid=r.docid) as e where
prod.t_results.docid=e.docid;
Nested Loop (cost=0.00..6072.00 rows=1000 width=138)
-> Nested Loop (cost=0.00..3046.00 rows=1000 width=16)
-> Seq Scan on termdate t (cost=0.00..20.00 rows=1000
width=12)
-> Index Scan using t_resultsid on t_results r
(cost=0.00..3.01 rows=1 width=4)
Index Cond: ("outer".docid = r.docid)
-> Index Scan using t_resultsid on t_results (cost=0.00..3.01
rows=1 width=122)
Index Cond: (t_results.docid = "outer".docid)
On Fri, 25 Jul 2003, Maksim Likharev wrote: > I have 2 queries, one is never returns, like explain shows 677195846.00 > cost > and another doing the same job works ( cost 6072.00 ) > > I do not understand one thing, why query number one, generates so > unbelievably > screwed up plan? > > why it does not use index? > > query # 1: > ------------------------------------------------------------------------ > ------ > explain update prod.t_results set expdate=e.termdate from > work.termdate e, prod.t_results r where e.docid=r.docid; This is almost certainly not what you meant. You haven't constrained the update table (t_results) to the results of the from. Adding t_results r to the from joins an additional copy of the table (it's not an alias for the update table). Why not just update prod.tresults set expdate=e.termdate from work.termdate where e.docid=t_results.docid; ?
My be I too spoiled by MS SQL Server, but does'nt
syntax:
update prod.t_results set expdate=e.termdate from
work.termdate e, prod.t_results r where e.docid=r.docid;
or
update prod.t_results set expdate=e.termdate from
work.termdate e inner join prod.t_results r on e.docid=r.docid;
is standard SQL-92 update FROM form?
just trying to understand.
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Friday, July 25, 2003 4:12 PM
To: Maksim Likharev
Cc: pgsql-general
Subject: Re: [GENERAL] Wacky query plan, why?
On Fri, 25 Jul 2003, Maksim Likharev wrote:
> I have 2 queries, one is never returns, like explain shows
677195846.00
> cost
> and another doing the same job works ( cost 6072.00 )
>
> I do not understand one thing, why query number one, generates so
> unbelievably
> screwed up plan?
>
> why it does not use index?
>
> query # 1:
>
------------------------------------------------------------------------
> ------
> explain update prod.t_results set expdate=e.termdate from
> work.termdate e, prod.t_results r where e.docid=r.docid;
This is almost certainly not what you meant. You haven't constrained
the update table (t_results) to the results of the from. Adding
t_results
r to the from joins an additional copy of the table (it's not an alias
for
the update table).
Why not just
update prod.tresults set expdate=e.termdate from work.termdate where
e.docid=t_results.docid;
?
Maksim Likharev wrote:
> My be I too spoiled by MS SQL Server, but does'nt
> syntax:
>
> update prod.t_results set expdate=e.termdate from
> work.termdate e, prod.t_results r where e.docid=r.docid;
> or
> update prod.t_results set expdate=e.termdate from
> work.termdate e inner join prod.t_results r on e.docid=r.docid;
>
> is standard SQL-92 update FROM form?
> just trying to understand.
13.10 <update statement: searched>
Function
Update rows of a table.
Format
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
So, for SQL92:
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
If a 'termdate.docid' does not necessarily exist for every
't_results.docid' then you must further qualify the update to ensure
expdate won't be set to NULL (or die trying):
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
)
WHERE EXISTS (
SELECT 1
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
That's SQL92 and it's ugly. I prefer the PostgreSQL extended form:
UPDATE prod.t_results
SET expdate = work.termdate.termdate
WHERE prod.t_results.docid = work.termdate.docid;
Hope that helps,
Mike Mascari
mascarm@mascari.com
On Fri, 25 Jul 2003, Maksim Likharev wrote: > My be I too spoiled by MS SQL Server, but does'nt > syntax: > > update prod.t_results set expdate=e.termdate from > work.termdate e, prod.t_results r where e.docid=r.docid; > or > update prod.t_results set expdate=e.termdate from > work.termdate e inner join prod.t_results r on e.docid=r.docid; > > is standard SQL-92 update FROM form? No. There is no standard SQL-92 update FROM syntax (nor SQL 99 AFAICS). I'd guess that it'd be standardized to do what we do for a query like the above because in one case the reference name for the table is t_results and in the other it's r.
Yes I see, no words about FROM cause in SQL92/99, but
it seems like Postgres supports that.
So bottom line:
insted of
update prod.t_results set fan = a.fullname, fin=i.fullname
from prod.t_results r inner join prod.t_agn a
on r.faid = a.aid
inner join prod.t_inv i
on r.fiid = i.iid
where r.docid = 22544257;
I should use
update prod.t_results set fan = a.fullname, fin=i.fullname
from prod.t_results r inner join prod.t_agn a
on r.faid = a.aid
inner join prod.t_inv i
on r.fiid = i.iid
where r.docid = 22544257 and prod.t_results.docid =
r.docid;
BTW, what it's doing in a first place, looks up tuples generated in FROM
clause
against prod.t_results table?
-----Original Message-----
From: Mike Mascari [mailto:mascarm@mascari.com]
Sent: Friday, July 25, 2003 5:25 PM
To: Maksim Likharev
Cc: Stephan Szabo; pgsql-general
Subject: Re: [GENERAL] Wacky query plan, why?
Maksim Likharev wrote:
> My be I too spoiled by MS SQL Server, but does'nt
> syntax:
>
> update prod.t_results set expdate=e.termdate from
> work.termdate e, prod.t_results r where e.docid=r.docid;
> or
> update prod.t_results set expdate=e.termdate from
> work.termdate e inner join prod.t_results r on e.docid=r.docid;
>
> is standard SQL-92 update FROM form?
> just trying to understand.
13.10 <update statement: searched>
Function
Update rows of a table.
Format
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
So, for SQL92:
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
If a 'termdate.docid' does not necessarily exist for every
't_results.docid' then you must further qualify the update to ensure
expdate won't be set to NULL (or die trying):
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
)
WHERE EXISTS (
SELECT 1
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
That's SQL92 and it's ugly. I prefer the PostgreSQL extended form:
UPDATE prod.t_results
SET expdate = work.termdate.termdate
WHERE prod.t_results.docid = work.termdate.docid;
Hope that helps,
Mike Mascari
mascarm@mascari.com
On Fri, 25 Jul 2003, Maksim Likharev wrote:
> Yes I see, no words about FROM cause in SQL92/99, but
> it seems like Postgres supports that.
Yeah, it's an extension to the standard behavior.
> So bottom line:
> insted of
>
> update prod.t_results set fan = a.fullname, fin=i.fullname
> from prod.t_results r inner join prod.t_agn a
> on r.faid = a.aid
> inner join prod.t_inv i
> on r.fiid = i.iid
> where r.docid = 22544257;
>
> I should use
>
> update prod.t_results set fan = a.fullname, fin=i.fullname
> from prod.t_results r inner join prod.t_agn a
> on r.faid = a.aid
> inner join prod.t_inv i
> on r.fiid = i.iid
> where r.docid = 22544257 and prod.t_results.docid =
> r.docid;
Why not just something like:
update prod.t_results set fan=a.fullname, fin=i.fullname
from prod.t_agn a, prod.t_inv i
where prod.t_results.faid = a.aid and
prod.t_results.fiid = i.iid and
prod.t_results.docid = 22544257;
I don't see much need to join a second copy of t_results into the query.
> BTW, what it's doing in a first place, looks up tuples generated in FROM
> clause
> against prod.t_results table?
AFAIK it's similar in behavior to if you'd written a select of the form
select * from
t_results, t_results r inner join t_agn a on r.faid=a.aid inner join
t_inv i on r.fiid = i.iid
where r.docid = 22544257;
You've got two copies of t_results being joined in the result and the
first is not constrained in any way so you get an "output" row for each
row of the inner join set for each row in t_results.