<div>Neither of the ST_Intersects clauses below invoke index usage according to explain output, despite docs saying they should automatically be doing bbox on the index;</div><br><div><div>explain analyze SELECT wastac.t_swath_metadata.swath_id AS wastac_t_swath_metadata_swath_id</div>
<div> FROM wastac.t_swath_metadata</div><div> JOIN wastac.t_tile_metadata ON wastac.t_swath_metadata.swath_id = wastac.t_tile_metadata.swath_id</div><div> JOIN wastac.t_tile_geometry ON wastac.t_tile_metadata.tile_id = wastac.t_tile_geometry.tile_id</div>
<div> AND wastac.t_tile_metadata.grid_id = wastac.t_tile_geometry.grid_id</div><div> WHERE wastac.t_swath_metadata.quicklook = True</div><div> AND ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143 -31.316101,116.751709 -31.381779))'))</div>
<div> AND <b><font class="Apple-style-span" color="#FF0000">ST_Intersects(border,</font></b>ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143 -31.316101,116.751709 -31.381779))'))</div>
<div> AND wastac.t_tile_metadata.ob_cloud_m > 2.0 GROUP BY wastac.t_swath_metadata.swath_id</div><div> HAVING count(wastac.t_tile_metadata.tile_id) = 95 LIMIT 20</div></div><div><br></div><div>One of the above ST_Intersects uses this (just note the border column/index):</div>
<div><br></div><div><div> \d wastac.t_tile_geometry</div><div> Table "wastac.t_tile_geometry"</div><div> Column | Type | Modifiers</div>
<div>---------+-------------------------+----------------------------------------------------------------------</div><div> tile_id | integer | not null</div><div> grid_id | integer | not null default nextval('wastac.t_grid_type_grid_id_seq'::regclass)</div>
<div><b> <font class="Apple-style-span" color="#FF0000">border | geography(Polygon,4326) |</font></b></div><div> centre | geography(Point,4326) |</div><div>Indexes:</div><div> "t_tile_geometry_pkey" PRIMARY KEY, btree (tile_id, grid_id)</div>
<div> <b><font class="Apple-style-span" color="#FF0000">"t_tile_geometry_border_key" gist (border)</font></b></div><div> "t_tile_geometry_centre_key" gist (centre)</div><div>Foreign-key constraints:</div>
<div> "fk_t_tile_geometry_1" FOREIGN KEY (grid_id) REFERENCES wastac.t_grid_type(grid_id)</div><div>Referenced by:</div><div> TABLE "wastac.t_tile_metadata" CONSTRAINT "fk_t_tile_metadata_2" FOREIGN KEY (tile_id, grid_id) REFERENCES wastac.t_tile_geometry(tile_id, grid_id)</div>
<div><br></div><div>Yet we see no evidence of GIST index usage here:</div><div><br></div><div><div> Limit (cost=0.00..2159649.87 rows=20 width=8) (actual time=4036.091..44743.940 rows=20 loops=1)</div><div> -> GroupAggregate (cost=0.00..1001537626.11 rows=9275 width=8) (actual time=4036.088..44743.894 rows=20 loops=1)</div>
<div> Filter: (count(t_tile_metadata.tile_id) = 95)</div><div> -> Nested Loop (cost=0.00..1001387855.03 rows=29926392 width=8) (actual time=53.319..44735.239 rows=11683 loops=1)</div><div> -> Nested Loop (cost=0.00..743388879.45 rows=29926392 width=12) (actual time=32.931..21859.582 rows=1719454 loops=1)</div>
<div> -> Index Scan using t_swath_metadata_pkey on t_swath_metadata (cost=0.00..669641.79 rows=9275 width=4) (actual time=0.765..255.304 rows=451</div><div> loops=1)</div><div> Filter: (quicklook AND st_intersects(swath_bounding, '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D4</div>
<div>0DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography))</div><div> -> Index Scan using t_tile_metadata_pkey on t_tile_metadata (cost=0.00..80037.21 rows=3227 width=12) (actual time=6.403..45.723 rows=3813 l</div>
<div>oops=451)</div><div> Index Cond: (t_tile_metadata.swath_id = t_swath_metadata.swath_id)</div><div> Filter: (t_tile_metadata.ob_cloud_m > 2::double precision)</div>
<div> -> Index Scan using t_tile_geometry_pkey on t_tile_geometry (cost=0.00..8.61 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1719454)</div><div> Index Cond: ((t_tile_geometry.tile_id = t_tile_metadata.tile_id) AND (t_tile_geometry.grid_id = t_tile_metadata.grid_id))</div>
<div> <font class="Apple-style-span" color="#FF0000"><b>Filter: st_intersects(t_tile_geometry.border, '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F5639356</b></font></div>
<div><font class="Apple-style-span" color="#FF0000"><b>40C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</b></font></div><div> Total runtime: 44744.241 ms</div>
<div>(14 rows)</div><div><br></div><div><br></div><div>However if I swap out ST_Intersects for &&, things start to look more familiar (note appearance of t_tile_geometry_border_key), but paradoxically the query is longer;</div>
<div><br></div><div><div> Limit (cost=46397.20..46397.42 rows=15 width=8) (actual time=65951.363..65951.692 rows=8 loops=1)</div><div> -> HashAggregate (cost=46397.20..46397.42 rows=15 width=8) (actual time=65951.361..65951.685 rows=8 loops=1)</div>
<div> Filter: (count(t_tile_metadata.tile_id) = 95)</div><div> -> Hash Join (cost=2986.26..46397.12 rows=15 width=8) (actual time=960.682..65886.428 rows=67553 loops=1)</div><div> Hash Cond: (t_tile_metadata.swath_id = t_swath_metadata.swath_id)</div>
<div> -> Nested Loop (cost=86.37..43490.92 rows=1644 width=8) (actual time=106.668..64931.709 rows=84257 loops=1)</div><div> Join Filter: (t_tile_metadata.grid_id = t_tile_geometry.grid_id)</div>
<div> -> <b><font class="Apple-style-span" color="#FF0000">Index Scan using t_tile_geometry_border_key on t_tile_geometry</font></b> (cost=0.00..28.97 rows=6 width=8) (actual time=42.340..121.791 rows=112 l</div>
<div>oops=1)</div><div> Index Cond: (border && '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5</div><div>C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</div>
<div> -> Bitmap Heap Scan on t_tile_metadata (cost=86.37..7216.39 rows=1818 width=12) (actual time=10.025..578.134 rows=752 loops=112)</div><div> Recheck Cond: (t_tile_metadata.tile_id = t_tile_geometry.tile_id)</div>
<div> Filter: (t_tile_metadata.ob_cloud_m > 2::double precision)</div><div> -> Bitmap Index Scan on t_tile_metadata_tile_id_key (cost=0.00..86.30 rows=1818 width=0) (actual time=8.693..8.693 rows=1884 loops=11</div>
<div>2)</div><div> Index Cond: (t_tile_metadata.tile_id = t_tile_geometry.tile_id)</div><div> -> Hash (cost=2897.85..2897.85 rows=163 width=4) (actual time=853.913..853.913 rows=2564 loops=1)</div>
<div> -> Bitmap Heap Scan on t_swath_metadata (cost=43.86..2897.85 rows=163 width=4) (actual time=454.656..851.229 rows=2564 loops=1)</div><div> Recheck Cond: (swath_bounding && '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1</div>
<div>B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</div><div> Filter: quicklook</div><div> -> Bitmap Index Scan on t_swath_metadata_swath_bounding_key (cost=0.00..43.82 rows=1001 width=0) (actual time=453.917..453.917 rows=3</div>
<div>574 loops=1)</div><div> Index Cond: (swath_bounding && '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C</div><div>0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)</div>
<div> Total runtime: 65952.140 ms</div><div>(21 rows)</div><div><br></div></div><div><br></div><div>What's going on - the difference in total cost above proves to me the indexes are not being used.</div><div><br></div>
<div><br></div></div></div>