I wrote:
> That ... is pretty quirky already. How did it prefer a scan with cost
> 19.32 over one with cost 9.39? Seems like we've got a bug here somewhere.
> The change in estimated rowcount is rather broken, too.
Ah, false alarm. I can reproduce your results if I stick an ANALYZE
between the first and second EXPLAIN. So probably your change in
estimated rowcount and hence cost can be explained by an auto-analyze
coming along at just the right time.
Also, if I fill the geom and location columns with non-null data,
the planner stops preferring those indexes.
So now I'm guessing that the OP's data *was* mostly null, and the
planner preferred the gist indexes because they were smallest,
and then tripped over the nonreturnable-column bug.
regards, tom lane
