Re: Any ideas how can I speed up this query?

>
> QUERY
>
> SELECT COUNT(*) FROM "occurrences" WHERE ("lat" >= -27.91550355958 AND "lat" <= -27.015680440420002 AND "lng" >= 152.13307044728307 AND "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 search key are found, and 0 otherwise. As your table is quite big, the size of the bitmap is getting bigger, then available memory 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 contain matching records. This means, that outer node Bitmap Heap Scan has to read all records from such block and re-check them. 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) so that 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.