Re: Postgres not using GiST index in a lateral join

> On 04 Mar 2015, at 22:18, Igor Stassiy <istassiy(at)gmail(dot)com> wrote:
>
> I would like to stop executing the query for a row of table "a" when a single row of "b" is found. This query would not stop
> processing but will filter all the rows that are found at the end of execution.
>
> Is there a way to express this without a subquery?

Does it? Because that would be somewhat surprising.

> On Wed, Mar 4, 2015 at 11:53 PM Paul Ramsey <pramsey(at)cleverelephant(dot)ca> wrote:
> Stop writing so many subqueries, think in joins; the poor planner!
>
> SELECT DISTINCT ON (a.id) a.id AS a_id, b.id AS b_id
> FROM a
> JOIN b
> ON ST_Contains(b.shape, a.shape)
> WHERE b.kind != 1
>
> Also, the DISTINCT ON syntax (PgSQL custom) lets you winnow a result
> set down to just one of the inputs.

> > -- for each row in A, select exactly one row in B (if there is one)
> > -- such that B contains geometry of A
> > EXPLAIN SELECT * FROM (SELECT A.id as aid, (SELECT B.id FROM B WHERE
> > ST_Contains(B.shape, A.shape) AND B.kind != 1 LIMIT 1) as bid FROM A) AS
> > TMP;
> >
> > which gives me { "Plan": { "Node Type": "Seq Scan", "Relation Name": "A",
> > "Startup Cost": 0.00, "Total Cost": 2606592.33, "Plan Rows": 549745, "Plan
> > Width": 1646, "Plans": [ { "Node Type": "Limit", "Parent Relationship":
> > "SubPlan", "Subplan Name": "SubPlan 1", "Startup Cost": 0.00, "Total Cost":
> > 4.68, "Plan Rows": 1, "Plan Width": 8, "Plans": [ { "Node Type": "Seq Scan",
> > "Parent Relationship": "Outer", "Relation Name": "B", "Startup Cost": 0.00,
> > "Total Cost": 4.68, "Plan Rows": 1, "Plan Width": 8, "Filter": "((shape &&
> > A.shape) AND _st_contains(shape, A.shape))" } ] } ] } }

How did your query plan end up in JSON notation? It's quite difficult to read like this.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.