This query uses the spatial index and it's quite fast:

    select a.the_geom from 
     chicago_major_streets a,
     stormevents_locations_2014 b
    where st_intersects(a.the_geom,b.the_geom)

  QUERY PLAN                                                               
 Nested Loop  (cost=0.28..32436.92 rows=88 width=137)
   ->  Seq Scan on chicago_major_streets a  (cost=0.00..1054.65 rows=16065 width=137)
   ->  Index Scan using stormevents_locations_2014_the_geom_geom_idx on stormevents_locations_2014 b  (cost=0.28..1.93 rows=2 width=32)
         Index Cond: (a.the_geom && the_geom)
         Filter: _st_intersects(a.the_geom, the_geom)
(5 rows)

However if I use a with clause index are ignored:

    with a as (select * from chicago_major_streets),
      b as (select * from stormevents_locations_2014)
    select a.the_geom from 
    where st_intersects(a.the_geom,b.the_geom);

QUERY PLAN                                         
Nested Loop  (cost=2534.91..232135969.50 rows=283954 width=32)
 Join Filter: ((a.the_geom && b.the_geom) AND _st_intersects(a.the_geom, b.the_geom))
 CTE a
   ->  Seq Scan on chicago_major_streets  (cost=0.00..1054.65 rows=16065 width=409)
 CTE b
   ->  Seq Scan on stormevents_locations_2014  (cost=0.00..1480.26 rows=53026 width=113)
 ->  CTE Scan on a  (cost=0.00..321.30 rows=16065 width=32)
 ->  CTE Scan on b  (cost=0.00..1060.52 rows=53026 width=32)
(8 rows)

Any ideas?

  • 2
    As far as I understand, the index is not used because your WHERE clause is outside the CTE's. The CTE's in effect forces the database engine to read both tables in their whole before evaluating any possible WHERE clause, but as both tables have been read in their entirety there is no reason to use the index. You could of course write your your original query as a CTE as with c as (select a.the_geom from chicago_major_streets a, stormevents_locations_2014 b where st_intersects(a.the_geom,b.the_geom)) May 18 '16 at 12:45
  • select the_geom from c apparently I edited my previous comment to many times, so just adding the last part of the query here. May 18 '16 at 13:11

By using CTEs you are forcing the planner to evaluate each CTE component without looking at any other part of the query. Once you've extracted the results from the tables using the CTEs the fact that there is an index is lost to the planner. You've created a new, non-indexed relation in the CTE.

In general, if you can write logic as a plain join, always do that. Only use CTEs and sub-queries as last resorts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.