Bitmap AND multicolumn index used !

From: Arnaud Lesauvage <arnaud(dot)lesauvage(at)laposte(dot)net>
To: pgadmin-support(at)postgresql(dot)org
Subject: Bitmap AND multicolumn index used !
Date: 2007-03-09 15:17:27
Message-ID: 45F17A87.3070808@laposte.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

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 && '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=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 && '0103000020777F000001000000050000000000000080841E4100000000D01253410000000080841E41000000009651884100000000804F2241000000007874534100000000804F224100000000D01253410000000080841E4100000000D0125341'::geometry)"
"Total runtime: 809.338 ms"

Thanks a lot for clarifying this !

Regards
--
Arnaud

Browse pgadmin-support by date

  From Date Subject
Next Message Ezequias Rodrigues da Rocha 2007-03-09 17:22:36 Statistics
Previous Message Laurent ROCHE 2007-03-09 14:17:04 Re : Changing owner objects