On Wed, 12 Mar 2008 11:48:24 +0100
Martijn van Oosterhout <kleptog@xxxxxxxxx> wrote:
> On Wed, Mar 12, 2008 at 11:40:18AM +0100, Ivan Sergio Borgonovo
> wrote:
> > I'd like to make this query work
> >
> > select 1,
> > st.Name, sm.Name, sm.MethodID, sm.Description,
> > pt.Name, pm.Name, pm.MethodID, pm.Description
> > from
> > shop_commerce_paymethods pm,
> > shop_commerce_shipmethods sm
> >
> > inner join shop_commerce_shiptypes st on
> > sm.TypeID=st.TypeID inner join shop_commerce_paytypes pt on
> > pm.TypeID=pt.TypeID where sm.MethodID=1 and pm.MethodID=1
> >
> > I can make it work renouncing to one *t.Name changing the order of
> > the FROM tables and skipping one join... but I can't have in one
> > run all I need.
>
> From my understanding of SQL join syntax, the above is parsed as:
>
> FROM pm,((sm inner join st) inner join pt)
>
> which means that pm isn't in scope when doing the inner join on pt.
> Perhaps this would owrk:
> FROM sm inner join st inner join pt inner join pm
one of the inner join doesn't have an on relationship.
As you could see in the other (longer) query I'm just trying to put in
the same row what would be
select 1,
t.Name, m.Name, m.MethodID, m.Description
from shop_commerce_shipmethods m
inner join shop_commerce_shiptypes t on m.TypeID=t.TypeID
where m.MethodID=1
+
select 1,
t.Name, m.Name, m.MethodID, m.Description
from shop_commerce_paymethods m
inner join shop_commerce_paytypes t on m.TypeID=t.TypeID
where m.MethodID=1
I don't want it to get it with a union since pay and ship are
associated.
The "natural" way to get them in one row would be to get the table
that link them shop_commerce_baskets as in the longer query.
But that query contains a lot of unions and loop etc... and I'd be
curious to see if fetching ShipMethodOnStockID, PayMethodOnStock, ...
in advance and using the above queries would make the query plan
simpler...
I'm attaching the query plan of the longer query since it is too
nested to just get pasted.
The scope of the "exercise" would be to avoid 2 scans of the
shop_commerce_baskets table.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Unique (cost=58.36..58.41 rows=2 width=884)
-> Sort (cost=58.36..58.37 rows=2 width=884)
Sort Key: "?column?", name, name, methodid, description, name, name, methodid, description
-> Append (cost=0.00..58.35 rows=2 width=884)
-> Subquery Scan "*SELECT* 1" (cost=0.00..29.18 rows=1 width=884)
-> Nested Loop (cost=0.00..29.17 rows=1 width=884)
-> Nested Loop (cost=0.00..23.33 rows=1 width=806)
-> Nested Loop (cost=0.00..17.49 rows=1 width=446)
-> Nested Loop (cost=0.00..11.66 rows=1 width=368)
-> Index Scan using shop_commerce_baskets_pkey on shop_commerce_baskets b (cost=0.00..5.82 rows=1 width=8)
Index Cond: (basketid = 3)
-> Index Scan using shop_commerce_paymethods_pkey on shop_commerce_paymethods pm (cost=0.00..5.82 rows=1 width=364)
Index Cond: (pm.methodid = "outer".paymethodonstockid)
-> Index Scan using shop_commerce_paytypes_pkey on shop_commerce_paytypes pt (cost=0.00..5.82 rows=1 width=86)
Index Cond: ("outer".typeid = pt.typeid)
-> Index Scan using shop_commerce_shipmethods_pkey on shop_commerce_shipmethods sm (cost=0.00..5.82 rows=1 width=364)
Index Cond: (sm.methodid = "outer".shipmethodonstockid)
-> Index Scan using shop_commerce_shiptypes_pkey on shop_commerce_shiptypes st (cost=0.00..5.82 rows=1 width=86)
Index Cond: ("outer".typeid = st.typeid)
-> Subquery Scan "*SELECT* 2" (cost=0.00..29.18 rows=1 width=884)
-> Nested Loop (cost=0.00..29.17 rows=1 width=884)
-> Nested Loop (cost=0.00..23.33 rows=1 width=806)
-> Nested Loop (cost=0.00..17.49 rows=1 width=446)
-> Nested Loop (cost=0.00..11.66 rows=1 width=368)
-> Index Scan using shop_commerce_baskets_pkey on shop_commerce_baskets b (cost=0.00..5.82 rows=1 width=8)
Index Cond: (basketid = 3)
-> Index Scan using shop_commerce_paymethods_pkey on shop_commerce_paymethods pm (cost=0.00..5.82 rows=1 width=364)
Index Cond: (pm.methodid = "outer".paymethodbackorderid)
-> Index Scan using shop_commerce_paytypes_pkey on shop_commerce_paytypes pt (cost=0.00..5.82 rows=1 width=86)
Index Cond: ("outer".typeid = pt.typeid)
-> Index Scan using shop_commerce_shipmethods_pkey on shop_commerce_shipmethods sm (cost=0.00..5.82 rows=1 width=364)
Index Cond: (sm.methodid = "outer".shipmethodbackorderid)
-> Index Scan using shop_commerce_shiptypes_pkey on shop_commerce_shiptypes st (cost=0.00..5.82 rows=1 width=86)
Index Cond: ("outer".typeid = st.typeid)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
