Re: PostgreSQL 9.3.4 Query Problems

I disabled the btree compound index to forced the planner to use the spatia=
l index and visa versa, and the select count returns zero rows in both thes=
e cases. =0A=
=0A=
I ran some other similar queries that did return rows and they matched in b=
oth cases (Using the gist index or Using the btree index).=0A=
=0A=
I wish we could avoid this type of query but these are being generated by H=
ibernate. We are now strongly considering dropping the gist index altogethe=
r, since queries on the compound index run so much faster and seem to retur=
n the same data.=0A=
=0A=
thanks for you help =0A=
________________________________________=0A=
From: Emre Hasegeli [emre@hasegeli.com]=0A=
Sent: Monday, July 21, 2014 11:33 AM=0A=
To: Burgess, Freddie=0A=
Cc: pgsql-bugs@postgresql.org=0A=
Subject: Re: [BUGS] PostgreSQL 9.3.4 Query Problems=0A=
=0A=
> Query planner is not selecting the most efficient index, can anything be =
done to correct this problem?=0A=
=0A=
Trimmed explain outputs:=0A=
=0A=
>          ->  Index Scan using sidx_sponser_report_y2014m06=0A=
>                Index Cond: (sponser_location && ...=0A=
>                Filter: ((origin_date_time >=3D ...=0A=
>                Rows Removed by Filter: 3849011=0A=
>  Total runtime: 63913.610 ms=0A=
=0A=
> -- Then I disable the spatial index=0A=
=0A=
>          ->  Index Scan using idx_sessiondatetime_rpi_sponser_report_y201=
4m06=0A=
>                Index Cond: ((session_uid =3D ...=0A=
>                Filter: ((sponser_location && ...=0A=
>                Rows Removed by Filter: 1128161=0A=
>  Total runtime: 1124.355 ms=0A=
=0A=
It looks like a problem caused by selectivity estimation.  PostgreSQL=0A=
will choose the index which will return less rows.  The second index=0A=
actually returns less rows than the first one, but probably=0A=
PostgreSQL does not know about it.  There is more chance that=0A=
the selectivity estimation function of the && operator misguides=0A=
the planner.  I do not know about PostGIS, but selectivity estimation=0A=
functions for geometrical data types in the core are just stubs=0A=
returning constants.  So, I would not expect much.=0A=
=0A=
Best solution I can think of is to avoid that kind of queries.=0A=
=0A=
pgsql-general@, pgsql-performance@ or maybe PostGIS mailing lists=0A=
seems like a better place to ask for help.=0A=