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? 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. > > P. > > > On Wed, Mar 4, 2015 at 6:36 AM, Igor Stassiy <istas...@gmail.com> wrote: > > Hello, > > > > I have a query plan optimization question. It is formatted nicely on > > > > http://stackoverflow.com/questions/28856452/postgres- > not-using-gist-index-in-lateral-join > > > > But here is a copy for the archive: > > > > Here is the setup: > > > > CREATE EXTENSION postgis; > > DROP TABLE IF EXISTS A; > > DROP TABLE IF EXISTS B; > > CREATE TABLE A(shape Geometry, id INT); > > CREATE TABLE B(shape Geometry, id INT, kind INT); > > CREATE INDEX ON A USING GIST (shape); > > CREATE INDEX ON B USING GIST (shape); > > > > I am running the following commands: > > > > ANALYZE A; > > ANALYZE B; > > > > -- 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))" } ] } ] } } > > > > > > Note that there is a sequential scan inside the lateral join, however > there > > is clearly an index available. However after setting > > > > set enable_seqscan=false; > > > > the index is being used. This actually affects runtime significantly > (around > > 3 times faster) and seems that postgres should figure things like that > > automatically. { "Plan": { "Node Type": "Seq Scan", "Relation Name": "A", > > "Startup Cost": 10000000000.00, "Total Cost": 10004716493.85, "Plan > Rows": > > 549450, "Plan Width": 1677, "Plans": [ { "Node Type": "Limit", "Parent > > Relationship": "SubPlan", "Subplan Name": "SubPlan 1", "Startup Cost": > 0.00, > > "Total Cost": 8.52, "Plan Rows": 1, "Plan Width": 8, "Plans": [ { "Node > > Type": "Index Scan", "Parent Relationship": "Outer", "Scan Direction": > > "NoMovement", "Index Name": "B_shape_idx", "Relation Name": "B", "Startup > > Cost": 0.00, "Total Cost": 8.52, "Plan Rows": 1, "Plan Width": 8, "Index > > Cond": "(shape && A.shape)", "Filter": "_st_contains(shape, A.shape)" } > ] } > > ] } } > > > > Is there any way to tell postgres to use index in a less hacky way? > Possibly > > by rewriting the query? From what I understand the use of set enable_... > is > > not recommended in production. > > > > When you actually run the commands above it will give > > > > { "Plan": { "Node Type": "Seq Scan", "Relation Name": "a", "Alias": "a", > > "Startup Cost": 0.00, "Total Cost": 10372.75, "Plan Rows": 1230, "Plan > > Width": 36, "Plans": [ { "Node Type": "Limit", "Parent Relationship": > > "SubPlan", "Subplan Name": "SubPlan 1", "Startup Cost": 0.14, "Total > Cost": > > 8.41, "Plan Rows": 1, "Plan Width": 4, "Plans": [ { "Node Type": "Index > > Scan", "Parent Relationship": "Outer", "Scan Direction": "NoMovement", > > "Index Name": "b_shape_idx", "Relation Name": "b", "Alias": "b", "Startup > > Cost": 0.14, "Total Cost": 8.41, "Plan Rows": 1, "Plan Width": 4, "Index > > Cond": "(shape && a.shape)", "Filter": "((kind <> 1) AND > _st_contains(shape, > > a.shape))" } ] } ] } } > > > > Unfortunately I cannot provide data to reproduce the query plan results. > > > > Thanks, > > Igor >
