Thread: Which Join is better
Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) & B ( 7 MB ) A has 10 columns & B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks
On 2 August 2011 08:42, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
Dear all,
Just want to know which join is better for querying data faster.
I have 2 tables A ( 70 GB ) & B ( 7 MB )
A has 10 columns & B has 3 columns.Indexes exist on both tables's ids.
select p.* from table A p, B q where p.id=q.id
or
select p.* from table B q , A p where q.id=p.id
Hi,
it really doesn't matter. PostgreSQL can reorder the joins as it likes.
And you can always check, but I think the plans will be the same.
regards
Szymon
El Martes 02 Agosto 2011, Adarsh Sharma escribió: > Dear all, > > Just want to know which join is better for querying data faster. > > I have 2 tables A ( 70 GB ) & B ( 7 MB ) > > A has 10 columns & B has 3 columns.Indexes exist on both tables's ids. > > select p.* from table A p, B q where p.id=q.id > > or > > select p.* from table B q , A p where q.id=p.id > > > Thanks Hi Adarsh, What does a "EXPLAIN ANALYZE" say after a VACCUM? -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.es
Unless you use the explicit join syntax: select p.* from A p join B q on (p.id = q.id) and also set join_collapse_limit= 1 The order of the joins is determined by the planner. Also explain is your friend :) ________________________________ From: Adarsh Sharma <adarsh.sharma@orkash.com> To: pgsql-performance@postgresql.org Sent: Monday, August 1, 2011 11:42 PM Subject: [PERFORM] Which Join is better Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) & B ( 7 MB ) A has 10 columns & B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance