[postgis-users] Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Dan Lyke danlyke at flutterby.com
Tue Jul 25 09:47:54 PDT 2017

I'm trying to figure out a performance issue between two quarters of
map data. Both are using PosgreSQL 9.5.4 on Amazon RDS. The current
quarter's data appears to be much slower than the old quarter's data.

VACUUM ANALYZE has been run.

EXPLAIN ... on the old data is doing a:

->  Bitmap Heap Scan on foo_line (cost=50.85..5380.25 rows=1346
    Recheck Cond: (way && '...'::geometry)
    Filter: (highway IS NOT NULL)
    ->  Bitmap Index Scan on foo_index  (cost=0.00..50.52 rows=1346
        Index Cond: (way && '...'::geometry)

And on the new data:

->  Index Scan using foo_index on foo_line  (cost=0.55..1346.36  
rows=332 width=304)
    Index Cond: (way &&  '...'::geometry)
    Filter: (highway IS NOT NULL)

Which looks to me like it's doing the filter before the Bitmap Index

In either case, the new database is substantially slower than the old
database, and I need to fix that, and I'm down to asking strangers on
the Internet while I continue to Google around for solutions.


More information about the postgis-users mailing list