Have you timed it both with and without the separate btree? It could
be that using the 2 indexes is actually faster.
On Mar 10, 2007, at 4:19 AM, Arnaud Lesauvage wrote:
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 also did 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 &&
'0103000020777F000001000000050000000000000080841E4100000000D0125341000
0000080841E41000000009651884100000000804F22410000000078745341000000008
04F224100000000D01253410000000080841E4100000000D0125341'::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=146683 loops=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.883 rows=1248343 loops=1)"
" Index Cond: (nw_geometry &&
'0103000020777F000001000000050000000000000080841E4100000000D0125341000
0000080841E41000000009651884100000000804F22410000000078745341000000008
04F224100000000D01253410000000080841E4100000000D0125341'::geometry)"
"Total runtime: 809.338 ms"
Thanks a lot for clarifying this !
Regards
--
Arnaud
---------------------------(end of
broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
