Search Postgresql Archives

Re: FROM + JOIN when more than one table in FROM

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux