Re: PostgreSQL 9.3.4 Query Problems

I disabled the btree compound index to forced the planner to use the spatial index and visa versa, and the select count returns zero rows in both these cases.

I ran some other similar queries that did return rows and they matched in both cases (Using the gist index or Using the btree index).

I wish we could avoid this type of query but these are being generated by Hibernate. We are now strongly considering dropping the gist index altogether, since queries on the compound index run so much faster and seem to return the same data.

thanks for you help
________________________________________
From: Emre Hasegeli [emre(at)hasegeli(dot)com]
Sent: Monday, July 21, 2014 11:33 AM
To: Burgess, Freddie
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] PostgreSQL 9.3.4 Query Problems

> 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.