Обсуждение: PostgreSQL 9.3.4 Query Problems
Query planner is not selecting the most efficient index, can anything be done to correct this problem?
Thanks
Thanks
Вложения
On Mon, Jul 21, 2014 at 2:07 AM, Burgess, Freddie <FBurgess@radiantblue.com= > wrote: > Query planner is not selecting the most efficient index, can anything be > done to correct this problem? Perhaps you should post your problem to PERFORM list, and follow the guideline at [1]. [1] https://wiki.postgresql.org/wiki/Slow_Query_Questions --=20 Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br n=C3=ADvel F! www.dextra.com.br/postgres
> 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.
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=