>
> QUERY
>
> SELECT COUNT(*) FROM "occurrences" WHERE ("lat" >= -27.91550355958 AND "lat" <= -27.015680440420002 AND "lng" >=
152.13307044728307AND "lng" <= 153.03137355271693 AND "category_id" = 1 AND (ST_Intersects(
ST_Buffer(ST_PointFromText('POINT(152.582222-27.465592)')::geography, 50000)::geography, location::geography)));
> How I can assist planner in providing better row estimates for Bitmap Heap Scan section?
By googling this phrase from your EXPLAIN: "Rows Removed by Index Recheck: 748669" - you can find this explanation:
http://stackoverflow.com/questions/26418715/postgresql-rows-removed-by-index
"The inner Bitmap Index Scan node is producing a bitmap, putting 1 to all the places where records that match your
searchkey are found, and 0 otherwise. As your table is quite big, the size of the bitmap is getting bigger, then
availablememory for these kind of operations, configured via work_mem, becomes small to keep the whole bitmap.
When in lack of a memory, inner node will start producing 1 not for records, but rather for blocks that are known to
containmatching records. This means, that outer node Bitmap Heap Scan has to read all records from such block and
re-checkthem. Obiously, there'll be some non-matching ones, and their number is what you see as Rows Removed by Index
Recheck."
Therefore, try substantially increasing your work_mem (use set..... so that it's on a per-session basis, not global)
sothat you don't have to read in all the rows to re-check them.
This is why Googling phrases from your explain before list-posting is always a good idea :-)
BTW - what are your statistics set to? If you have a huge table, it can be worth raising them from the default.
http://www.postgresql.org/docs/9.4/static/planner-stats.html
ALTER TABLE SET STATISTICS, try raising this to 1000.
> POSTGRESQL VERSION INFO
For postgis-related questions, remember to also include the postgis version.
Hope this helps and good luck
Graeme Bell.