gistfile1.txt

Why are indicies being handled differently in these cases?  Below are three different but equivalent queries i would think should handle indicies the same:

Indicies:

Locations:
Indexes:
    "locations_pkey" PRIMARY KEY, btree (id)
    "index_locations_on_simplified_polygon" gist (simplified_polygon)
Check constraints:
    "enforce_srid_simplified_polygon" CHECK (st_srid(simplified_polygon) = 4326)

Units:
Indexes:
    "units_pkey" PRIMARY KEY, btree (id)
    "index_units_on_coordinates" gist (coordinates)
Check constraints:
    "enforce_dims_coordinates" CHECK (st_ndims(coordinates) = 2)
    "enforce_geotype_coordinates" CHECK (geometrytype(coordinates) = 'POINT'::text OR coordinates IS NULL)
    "enforce_srid_coordinates" CHECK (st_srid(coordinates) = 4326)

Fields that matter:
locations.simplified_polygon (geom of type polygon in this case representing a city)
units.coordinates (lat,lon point)


Queries:

Uses Index:  (polygon is joined from another table)
explain analyze select units.id from units, locations where (ST_Intersects(locations.simplified_polygon,units.coordinates)) and locations.id = 446
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..54.70 rows=40 width=4) (actual time=0.010..0.010 rows=0 loops=1)
   Join Filter: _st_intersects(locations.polygon, units.coordinates)
   ->  Index Scan using locations_pkey on locations  (cost=0.00..8.07 rows=1 width=74384) (actual time=0.008..0.008 rows=0 loops=1)
         Index Cond: (id = 446)
   ->  Index Scan using index_units_on_coordinates on units  (cost=0.00..46.09 rows=10 width=104) (never executed)
         Index Cond: (locations.polygon && units.coordinates)
 Total runtime: 0.146 ms
(7 rows)

Uses some indices: (polygon in hex format included in query)
explain analyze select units.id from units, locations where (ST_Intersects('0106000020E61000000500000001030000000100000004000000E6FFFF7FE5BF5EC00300004006DA42400900008097BF5EC000000060C0D94240FEFFFF1FCEBF5EC00600004055D94240E6FFFF7FE5BF5EC00300004006DA424001030000000100000004000000180000A080955EC0FFFFFF3F83E54240411180C0EB945EC07261F88DC9E342401D0000608C955EC00000006000E44240180000A080955EC0FFFFFF3F83E54240010300000001000000090000001D000060A8975EC01000008009E74240F7FFFF7F14985EC0FBFFFFDFA7E74240E6FFFF7F51985EC00500008050EA42400B000060E9975EC0F9FFFFFF81EA4240000000005C975EC0FBFFFF7FF7E84240ECFFFF1FD3975EC006000040F5E74240120000001F975EC00100002039E842400000000040975EC0050000202CE742401D000060A8975EC01000008009E7424001030000000100000004000000DDFFFFFFADC65EC0F1FFFF3F53E34240120000007BC65EC0FCFFFF9FD0E242400F000020EDC65EC004000000BBE24240DDFFFFFFADC65EC0F1FFFF3F53E3424001030000000100000012000000EE3ACA0C4EA05EC018A9DBDAA3DA4240ECFFFF1F1BA15EC000000060F8E34240E8FFFF5F87A05EC0FDFFFF5FEDE44240160000C0569F5EC0FEFFFF1F1AE54240E6FFFF7FD59E5EC0EDFFFFDFCBE742401A000080E29D5EC0010000C0F8E64240020000E0159A5EC0FEFFFF7F36E742400B000060F5985EC0FFFFFFDFE2E44240FEFFFF1F92985EC0020000E051E0424009000080DB985EC0FDFFFFBFB9DF4240070000A025985EC004000060ABDD4240F5FFFF9F0E975EC0FAFFFFBF32DD4240FEFFFF1F96975EC0FEFFFF1FAADB42400D00004043985EC0FAFFFF1FFBDB4240ECFFFF1F47985EC000000000BCDC4240FEFFFF1FDE985EC0030000A06ADC42402856D5AC21995EC01ADF1797AADA4240EE3ACA0C4EA05EC018A9DBDAA3DA4240', units.coordinates)) and locations.id = 446;

                           QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=265.85..9686.10 rows=836 width=4) (actual time=0.010..0.010 rows=0 loops=1)
   ->  Index Scan using locations_pkey on locations  (cost=0.00..8.07 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
         Index Cond: (id = 446)
   ->  Bitmap Heap Scan on units  (cost=265.85..9675.52 rows=836 width=4) (never executed)
         Recheck Cond: ('0106000020E61000000500000001030000000100000004000000E6FFFF7FE5BF5EC00300004006DA42400900008097BF5EC000000060C0D94240FEFFFF1FCEBF5EC00600004055D94240E6FFFF7FE5BF5EC00300004006DA424001030000000100000004000000180000A080955EC0FFFFFF3F83E54240411180C0EB945EC07261F88DC9E342401D0000608C955EC00000006000E44240180000A080955EC0FFFFFF3F83E54240010300000001000000090000001D000060A8975EC01000008009E74240F7FFFF7F14985EC0FBFFFFDFA7E74240E6FFFF7F51985EC00500008050EA42400B000060E9975EC0F9FFFFFF81EA4240000000005C975EC0FBFFFF7FF7E84240ECFFFF1FD3975EC006000040F5E74240120000001F975EC00100002039E842400000000040975EC0050000202CE742401D000060A8975EC01000008009E7424001030000000100000004000000DDFFFFFFADC65EC0F1FFFF3F53E34240120000007BC65EC0FCFFFF9FD0E242400F000020EDC65EC004000000BBE24240DDFFFFFFADC65EC0F1FFFF3F53E3424001030000000100000012000000EE3ACA0C4EA05EC018A9DBDAA3DA4240ECFFFF1F1BA15EC000000060F8E34240E8FFFF5F87A05EC0FDFFFF5FEDE44240160000C0569F5EC0FEFFFF1F1AE54240E6FFFF7FD59E5EC0EDFFFFDFCBE742401A000080E29D5EC0010000C0F8E64240020000E0159A5EC0FEFFFF7F36E742400B000060F5985EC0FFFFFFDFE2E44240FEFFFF1F92985EC0020000E051E0424009000080DB985EC0FDFFFFBFB9DF4240070000A025985EC004000060ABDD4240F5FFFF9F0E975EC0FAFFFFBF32DD4240FEFFFF1F96975EC0FEFFFF1FAADB42400D00004043985EC0FAFFFF1FFBDB4240ECFFFF1F47985EC000000000BCDC4240FEFFFF1FDE985EC0030000A06ADC42402856D5AC21995EC01ADF1797AADA4240EE3ACA0C4EA05EC018A9DBDAA3DA4240'::geometry && units.coordinates)
         Filter: _st_intersects('0106000020E61000000500000001030000000100000004000000E6FFFF7FE5BF5EC00300004006DA42400900008097BF5EC000000060C0D94240FEFFFF1FCEBF5EC00600004055D94240E6FFFF7FE5BF5EC00300004006DA424001030000000100000004000000180000A080955EC0FFFFFF3F83E54240411180C0EB945EC07261F88DC9E342401D0000608C955EC00000006000E44240180000A080955EC0FFFFFF3F83E54240010300000001000000090000001D000060A8975EC01000008009E74240F7FFFF7F14985EC0FBFFFFDFA7E74240E6FFFF7F51985EC00500008050EA42400B000060E9975EC0F9FFFFFF81EA4240000000005C975EC0FBFFFF7FF7E84240ECFFFF1FD3975EC006000040F5E74240120000001F975EC00100002039E842400000000040975EC0050000202CE742401D000060A8975EC01000008009E7424001030000000100000004000000DDFFFFFFADC65EC0F1FFFF3F53E34240120000007BC65EC0FCFFFF9FD0E242400F000020EDC65EC004000000BBE24240DDFFFFFFADC65EC0F1FFFF3F53E3424001030000000100000012000000EE3ACA0C4EA05EC018A9DBDAA3DA4240ECFFFF1F1BA15EC000000060F8E34240E8FFFF5F87A05EC0FDFFFF5FEDE44240160000C0569F5EC0FEFFFF1F1AE54240E6FFFF7FD59E5EC0EDFFFFDFCBE742401A000080E29D5EC0010000C0F8E64240020000E0159A5EC0FEFFFF7F36E742400B000060F5985EC0FFFFFFDFE2E44240FEFFFF1F92985EC0020000E051E0424009000080DB985EC0FDFFFFBFB9DF4240070000A025985EC004000060ABDD4240F5FFFF9F0E975EC0FAFFFFBF32DD4240FEFFFF1F96975EC0FEFFFF1FAADB42400D00004043985EC0FAFFFF1FFBDB4240ECFFFF1F47985EC000000000BCDC4240FEFFFF1FDE985EC0030000A06ADC42402856D5AC21995EC01ADF1797AADA4240EE3ACA0C4EA05EC018A9DBDAA3DA4240'::geometry, units.coordinates)
         ->  Bitmap Index Scan on index_units_on_coordinates  (cost=0.00..265.81 rows=2507 width=0) (never executed)
               Index Cond: ('0106000020E61000000500000001030000000100000004000000E6FFFF7FE5BF5EC00300004006DA42400900008097BF5EC000000060C0D94240FEFFFF1FCEBF5EC00600004055D94240E6FFFF7FE5BF5EC00300004006DA424001030000000100000004000000180000A080955EC0FFFFFF3F83E54240411180C0EB945EC07261F88DC9E342401D0000608C955EC00000006000E44240180000A080955EC0FFFFFF3F83E54240010300000001000000090000001D000060A8975EC01000008009E74240F7FFFF7F14985EC0FBFFFFDFA7E74240E6FFFF7F51985EC00500008050EA42400B000060E9975EC0F9FFFFFF81EA4240000000005C975EC0FBFFFF7FF7E84240ECFFFF1FD3975EC006000040F5E74240120000001F975EC00100002039E842400000000040975EC0050000202CE742401D000060A8975EC01000008009E7424001030000000100000004000000DDFFFFFFADC65EC0F1FFFF3F53E34240120000007BC65EC0FCFFFF9FD0E242400F000020EDC65EC004000000BBE24240DDFFFFFFADC65EC0F1FFFF3F53E3424001030000000100000012000000EE3ACA0C4EA05EC018A9DBDAA3DA4240ECFFFF1F1BA15EC000000060F8E34240E8FFFF5F87A05EC0FDFFFF5FEDE44240160000C0569F5EC0FEFFFF1F1AE54240E6FFFF7FD59E5EC0EDFFFFDFCBE742401A000080E29D5EC0010000C0F8E64240020000E0159A5EC0FEFFFF7F36E742400B000060F5985EC0FFFFFFDFE2E44240FEFFFF1F92985EC0020000E051E0424009000080DB985EC0FDFFFFBFB9DF4240070000A025985EC004000060ABDD4240F5FFFF9F0E975EC0FAFFFFBF32DD4240FEFFFF1F96975EC0FEFFFF1FAADB42400D00004043985EC0FAFFFF1FFBDB4240ECFFFF1F47985EC000000000BCDC4240FEFFFF1FDE985EC0030000A06ADC42402856D5AC21995EC01ADF1797AADA4240EE3ACA0C4EA05EC018A9DBDAA3DA4240'::geometry && units.coordinates)
 Total runtime: 0.047 ms
(9 rows)


Doesn't use index: (polygon included from sub query)
explain analyze select units.id from units where (ST_Intersects((select simplified_polygon from locations where id = 467), coordinates));

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on units  (cost=8.07..380984.12 rows=642378 width=4) (actual time=4.316..17060.913 rows=6021 loops=1)
   Filter: st_intersects($0, coordinates)
   InitPlan 1 (returns $0)
     ->  Index Scan using locations_pkey on locations  (cost=0.00..8.07 rows=1 width=1810) (actual time=0.017..0.018 rows=1 loops=1)
           Index Cond: (id = 467)
 Total runtime: 17078.799 ms
(6 rows)