Hi list !
I have a quite large table with a PostGIS-geometry field (~25M rows) representing road segments.
The segments are classified in 9 classes (from 0 to 8), based on their importance.
I am trying some different methods for optimizing queries on this table.
I decided to try with a multicolumn gist index (geometry first, the the road class), and to cluster the table on this
index.
I also added a simple btree index on the road class.
I thought that this index would be quite useless becauses there are only 9 different values for it, but I created it
anyway.
To my surprise, a query with a criteria on the geometry AND on the class did not only use the multicolumn index, but
alsodid a bitmap with the btree index !
It did not even use the second column of the multicolumn index !
Why is it so ? Should I simply remove this second column ?
EXPLAIN analyze
SELECT nw_geometry
FROM nw
WHERE frc=0
AND nw_geometry && GeomFromText('POLYGON((500000 5000000,500000 51000000,600000 5100000,600000 5000000,500000
5000000))',32631)
"Bitmap Heap Scan on nw (cost=48355.46..70461.85 rows=5831 width=153) (actual time=690.933..806.038 rows=11029
loops=1)"
" Recheck Cond: (frc = 0)"
" Filter: (nw_geometry &&
'0103000020777F000001000000050000000000000080841E4100000000D01253410000000080841E41000000009651884100000000804F2241000000007874534100000000804F224100000000D01253410000000080841E4100000000D0125341'::geometry)"
" -> BitmapAnd (cost=48355.46..48355.46 rows=5831 width=0) (actual time=688.743..688.743 rows=0 loops=1)"
" -> Bitmap Index Scan on nw_frc_btree (cost=0.00..2493.09 rows=134651 width=0) (actual time=52.358..52.358
rows=146683loops=1)"
" Index Cond: (frc = 0)"
" -> Bitmap Index Scan on nw_geometry_frc_gist (cost=0.00..45859.21 rows=1166186 width=0) (actual
time=609.883..609.883rows=1248343 loops=1)"
" Index Cond: (nw_geometry &&
'0103000020777F000001000000050000000000000080841E4100000000D01253410000000080841E41000000009651884100000000804F2241000000007874534100000000804F224100000000D01253410000000080841E4100000000D0125341'::geometry)"
"Total runtime: 809.338 ms"
Thanks a lot for clarifying this !
Regards
--
Arnaud