Unnecessary repeat condition for a self inner join - Mailing list pgsql-sql
| From | Robins Tharakan |
|---|---|
| Subject | Unnecessary repeat condition for a self inner join |
| Date | |
| Msg-id | 36af4bed0807110926w1fe444b3i5b2bd12effb958e3@mail.gmail.com Whole thread Raw |
| Responses |
Re: Unnecessary repeat condition for a self inner join
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
<span style="font-family: verdana,sans-serif;">Hi,</span><br style="font-family: verdana,sans-serif;" /><br style="font-family:verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;">I am not sure if this is a simple(... stupid) question but I just wasted two hours optimizing a query, so I thought I should drop in to ask.</span><brstyle="font-family: verdana,sans-serif;" /><br style="font-family: verdana,sans-serif;" /><span style="font-family:verdana,sans-serif;">The only difference between query1 and query2 (below) is that despite an explicitINNER JOIN, I have repeated the same condition for n2 (as given for n1) and this makes a whole lot of differencein performance (since it now uses the same index for n2 that it is using for n1).</span><br style="font-family:verdana,sans-serif;" /><br style="font-family: verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;">Incase of an INNER JOIN, shouldn't the second condition (in Query2) be unnecessary ? <br />Or am I beingunreasonable in this expectation ?</span><br style="font-family: verdana,sans-serif;" /><br style="font-family: verdana,sans-serif;"/><span style="font-family: verdana,sans-serif;">Regards,</span><br style="font-family: verdana,sans-serif;"/><b style="font-family: verdana,sans-serif;">Robins Tharakan</b><br /><br /><span style="font-family:verdana,sans-serif;">p.s.: The query below is just a simplification, and provides only EXPLAIN, but Ithink an EXPLAIN ANALYSE should be unnecessary here. In case anyone still needs it, please do tell.</span><br /><br /><bstyle="font-family: courier new,monospace;">Query 1</b><span style="font-family: courier new,monospace;">:</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">SELECT n1.scheme_code</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">FROM nav n1</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;"> INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">WHEREn1.scheme_code BETWEEN 100 AND 200</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">"Merge Join (cost=903471.23..10248343.37rows=622920912 width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">" Merge Cond: (n1.scheme_code = n2.scheme_code)"</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> Sort (cost=110929.32..111458.60rows=211712 width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">" Sort Key: n1.scheme_code"</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">" -> Bitmap Heap Scan on nav n1 (cost=8623.86..92201.54rows=211712 width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">" Recheck Cond: ((scheme_code >= 100) AND (scheme_code <= 200))"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> Bitmap Index Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" Index Cond: ((scheme_code >= 100) AND (scheme_code <= 200))"</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> Sort (cost=792541.91..805391.17rows=5139702 width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">" Sort Key: n2.scheme_code"</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">" -> Seq Scan on nav n2 (cost=0.00..131799.02rows=5139702 width=4)"</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><b style="font-family: couriernew,monospace;">Query 2</b><span style="font-family: courier new,monospace;">:</span><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">SELECTn1.scheme_code</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;">FROM nav n1</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;"> INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">WHERE n1.scheme_code BETWEEN 100 AND 200</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> AND n2.scheme_code BETWEEN100 AND 200</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> </span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"Merge Join (cost=221858.63..607790.72 rows=25659043width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" Merge Cond: (n2.scheme_code = n1.scheme_code)"</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">" -> Sort (cost=110929.32..111458.60 rows=211712 width=4)"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" Sort Key: n2.scheme_code"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">" -> Bitmap Heap Scan on nav n2 (cost=8623.86..92201.54 rows=211712width=4)"</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" Recheck Cond: ((scheme_code >= 100) AND (scheme_code <= 200))"</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> BitmapIndex Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)"</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">" Index Cond: ((scheme_code >= 100)AND (scheme_code <= 200))"</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;">" -> Sort (cost=110929.32..111458.60 rows=211712 width=4)"</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">" Sort Key: n1.scheme_code"</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> Bitmap HeapScan on nav n1 (cost=8623.86..92201.54 rows=211712 width=4)"</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">" Recheck Cond: ((scheme_code >= 100) AND (scheme_code<= 200))"</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" -> Bitmap Index Scan on pk_fs_nav (cost=0.00..8570.94 rows=211712 width=0)"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">" Index Cond: ((scheme_code >= 100) AND (scheme_code <= 200))"</span><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br />