Date: Thu, 19 Mar 2015 12:15:33 -0700 (MST)
From: Jake Magner <jakemagner90@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1426792533879-5842633.post@n5.nabble.com>
In-Reply-To: <1426783249710-5842603.post@n5.nabble.com>
References: <1426746214298-5842523.post@n5.nabble.com>
 <30571.1426775332@sss.pgh.pa.us> <1426783249710-5842603.post@n5.nabble.com>
Subject: Re: Merge Join chooses very slow index scan
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I think I understand now after reading the notes here on the merge join
algorithm: 

https://github.com/postgres/postgres/blob/4ea51cdfe85ceef8afabceb03c446574daa0ac23/src/backend/executor/nodeMergejoin.c

The index scanning node doesn't know the max id of the vehicle table and so
can't know when to stop looking for the next tuple that matches the join.
Doesn't seem like any easy way to improve that case. I imagine it is a
fairly common pattern though. Any time metadata about entities modeled in
different tables, is stored in the same table, and the distribution of the
number of each entity type is skewed, this situation will arise.

Best,
Jake


--
View this message in context: http://postgresql.nabble.com/Merge-Join-chooses-very-slow-index-scan-tp5842523p5842633.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance