Re: Bitmap AND multicolumn index used !
От | Jim Nasby |
---|---|
Тема | Re: Bitmap AND multicolumn index used ! |
Дата | |
Msg-id | 1FBE69D3-29E6-4EED-BFF7-4A6C3898C6BC@decibel.org обсуждение исходный текст |
Ответ на | Bitmap AND multicolumn index used ! (Arnaud Lesauvage <arnaud.lesauvage@laposte.net>) |
Список | pgsql-general |
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@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
В списке pgsql-general по дате отправления: