I got the query plan using: EXECUTE EXPLAIN (format json) ... INTO query_plan; RAISE NOTICE query_plan;
since by default EXPLAIN returns a set of rows and I'd have to create a table for storing that. As I mentioned in the original email, the queries and output is nicely formatted at: http://stackoverflow.com/questions/28856452/postgres-not-using-gist-index-in-lateral-join On Thu, Mar 5, 2015 at 11:54 AM Alban Hertroys <haram...@gmail.com> wrote: > > > On 04 Mar 2015, at 22:18, Igor Stassiy <istas...@gmail.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 <pram...@cleverelephant.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. > >
