> Query planner is not selecting the most efficient index, can anything be done to correct this problem?
Trimmed explain outputs:
> -> Index Scan using sidx_sponser_report_y2014m06
> Index Cond: (sponser_location && ...
> Filter: ((origin_date_time >= ...
> Rows Removed by Filter: 3849011
> Total runtime: 63913.610 ms
> -- Then I disable the spatial index
> -> Index Scan using idx_sessiondatetime_rpi_sponser_report_y2014m06
> Index Cond: ((session_uid = ...
> Filter: ((sponser_location && ...
> Rows Removed by Filter: 1128161
> Total runtime: 1124.355 ms
It looks like a problem caused by selectivity estimation. PostgreSQL
will choose the index which will return less rows. The second index
actually returns less rows than the first one, but probably
PostgreSQL does not know about it. There is more chance that
the selectivity estimation function of the && operator misguides
the planner. I do not know about PostGIS, but selectivity estimation
functions for geometrical data types in the core are just stubs
returning constants. So, I would not expect much.
Best solution I can think of is to avoid that kind of queries.
pgsql-general@, pgsql-performance@ or maybe PostGIS mailing lists
seems like a better place to ask for help.