PostgreSQL 9.3.4 Query Problems

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]https://wiki.postgresql.org/wiki/Slow_Query_Questions.

[1]: https://wiki.postgresql.org/wiki/Slow_Query_Questions

--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível 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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@hasegeli.com]
Sent: Monday, July 21, 2014 11:33 AM
To: Burgess, Freddie
Cc: pgsql-bugs@postgresql.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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs