Re: Perfomance of IN-clause with many elements and possible solutions

From: Dmitry Lazurkin <dilaz03(at)gmail(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Perfomance of IN-clause with many elements and possible solutions
Date: 2017-07-24 22:12:04
Message-ID: 7a3bdac6-d562-8173-e8c6-09a9a5bc4876@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 25.07.2017 00:31, David G. Johnston wrote:
>
> Basically you want to write something like:
>
> SELECT *
> FROM ids
> JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id <http://ids.id>)​
>
> or
>
> WITH vc AS (SELECT vid FROM .... ORDER BY ... LIMIT )
> SELECT *
> FROM ids
> JOIN vc ON (vid = ids.id <http://ids.id>)
>

This query uses JOIN plan node as IN (VALUES ...).

And I have one question. I don't understand why IN-VALUES doesn't use
Semi-Join? PostgreSQL has Hash Semi-Join... For which task the database
has node of this type?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2017-07-24 22:15:03 Re: Perfomance of IN-clause with many elements and possible solutions
Previous Message Dmitry Lazurkin 2017-07-24 21:56:43 Re: Perfomance of IN-clause with many elements and possible solutions