Re: How to use brin indexes?

On 11/21/2014 09:59 PM, hubert depesz lubaczewski wrote:
> I tried to add BRIN index on very simple table, to test it for "Waiting
> for" blogpost, btu I can't get it to work.
>
> I got newest git head, and ran:
>
> $ create table t (id int8);
> CREATE TABLE
> (depesz(at)[local]:5930) 20:56:22 [depesz]
> $ insert into t select generate_series(1,1000000);
> INSERT 0 1000000
> (depesz(at)[local]:5930) 20:56:31 [depesz]
> $ create index b on t using brin (id);
> CREATE INDEX
> (depesz(at)[local]:5930) 20:56:42 [depesz]
> $ vacuum ANALYZE t;
> VACUUM
> (depesz(at)[local]:5930) 20:56:49 [depesz]
> $ explain analyze select * from t where id = 1224;
> QUERY
> PLAN
> ------------------------------------------------------------------------------------------------
> Seq Scan on t (cost=0.00..16925.00 rows=1 width=8) (actual
> time=0.278..59.242 rows=1 loops=1)
> Filter: (id = 1224)
> Rows Removed by Filter: 999999
> Planning time: 0.186 ms
> Execution time: 59.272 ms
> (5 rows)
>
> (depesz(at)[local]:5930) 20:56:58 [depesz]
> $ drop index b;
> DROP INDEX
> (depesz(at)[local]:5930) 20:57:22 [depesz]
> $ create index b on t using brin (id) with (pages_per_range=1);
> CREATE INDEX
> (depesz(at)[local]:5930) 20:57:35 [depesz]
> $ vacuum ANALYZE t;
> VACUUM
> (depesz(at)[local]:5930) 20:57:38 [depesz]
> $ explain analyze select * from t where id = 1224;
> QUERY
> PLAN
> ------------------------------------------------------------------------------------------------
> Seq Scan on t (cost=0.00..16925.00 rows=1 width=8) (actual
> time=0.124..56.876 rows=1 loops=1)
> Filter: (id = 1224)
> Rows Removed by Filter: 999999
> Planning time: 0.044 ms
> Execution time: 56.886 ms
> (5 rows)
>
> What is the problem? Why isn't the brin index used?

The minmax operator families don't include any cross-type operators. I'm
not exactly sure why not.. Alvaro?

Anyway, try "select * from t where id = 1224::int8"

- Heikki