indexing - postgis spatial query assistance -

assuming have 3 tables:

• a. municipalities (multipolygon)
• b. postcode centroids (point)
• c. user data (point)

entries (c) match entries on (b) fk (code).

i looking efficient way to:

count number of user data (c) in municipalities (a) using st_contains.

but

here catch:

if entry in c null (or matches condition) use if exists matching entry in b using fk !!!

currently have tried various patterns , although spatially querying & b , & c both sub-second, once add them in 1 query (goal) outcome on 4secs

sample of i've tried:

this worse (60+ secs):

select     a.*,     (select          (select case when c.geom null b.geom else c.geom end          c left join b on c.id=b.id) b           st_contains(a.geom, b.geom)     ) count     

this 15 sec:

select      a.id, ..., -- other fields     count(b.geom)     a,     (select case when c.geom null b.geom else c.geom end      c left join b on c.id=b.id) b     st_contains(a.geom, b.geom) group     a.id, ... -- other fields 

as said

select count(*) left join b on st_contains(a.geom, b.geom) 

and

select count(*) left join c on st_contains(a.geom, c.geom) 

both return in under second.

all indexes in place foreign key (b.id = c.id)

thanks

did make indexes a.geom , b.geom?

it be

create index idx_a on using gist ( geom ); vacuum analyze (geom); create index idx_b on b using gist ( geom ); vacuum analyze b (geom);