Re: Bitmap AND multicolumn index used ! - Mailing list 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)